Hey there,

I googled myself to the point where I don't find the answer and my comprehension of the formula build up is not sufficient.

I am making a dynamic lookup and connecting several data columns into an overview based upon a weeknumber.

What I have;

The list under AD5 is picked up in AK6:AK19 with following formula (which is pulled through to the next 2 cells):

Named ranges in the formula;

employee: AK6:AK19

week: AI6:AI19

team: AJ6:AJ19

What I am now trying to accomplish is to also put the remark in col AE, based upon the list of names in AD.

I tried adapting the above formula as following (and pulled through to the next 2 cells)

Named range added in the formula;

remark: AL6:AL19

This does not work since the matrixes are not the same anymore, which is needed for the formula to work, as I understood it while googling.

Anyone?

If you cannot see the images, you can find an example file here:

google sheets example file

Corrected AF to AE reference

I googled myself to the point where I don't find the answer and my comprehension of the formula build up is not sufficient.

I am making a dynamic lookup and connecting several data columns into an overview based upon a weeknumber.

What I have;

The list under AD5 is picked up in AK6:AK19 with following formula (which is pulled through to the next 2 cells):

Code:

`=IFERROR(INDEX(employee,MATCH(0,countif($AD$5:AD5,employee)+IF(week<>$B$3,1;0)+IF(team<>$B$6,1;0);0)),"")`

employee: AK6:AK19

week: AI6:AI19

team: AJ6:AJ19

What I am now trying to accomplish is to also put the remark in col AE, based upon the list of names in AD.

I tried adapting the above formula as following (and pulled through to the next 2 cells)

Code:

`=IFERROR(INDEX(remark,MATCH(0,countif($AE$5:AE5,remark)+IF(employee<>AD6;1;0)+IF(week<>$B$3,1;0)+IF(team<>$B$6,1;0);0)),"")`

remark: AL6:AL19

This does not work since the matrixes are not the same anymore, which is needed for the formula to work, as I understood it while googling.

Anyone?

**EDIT:**If you cannot see the images, you can find an example file here:

google sheets example file

Corrected AF to AE reference

Last edited: