Particular Cell Reference

[Tripp]

Hello,

I am trying to find methods of referring to a cell reference inside an INDEX MATCH function. I have attached a small sample of what I would like to achieve.

Essentially I want to be able to copy the formula down/ paste it down in groups and it always refers to the correct Name under the Name column.

Something like first row is cell to the right, second row -1 row from previous, third row -2 from previous and so on.

Regards,
Tripp

 

[Tripp]

I've found using the OFFSET function works well but interested if anyone has more suggestions.

 

[bosco_yip]

Perhaps………

In D13, copied down :

=IF(A13="","",INDEX($B$5:$C$8,MATCH(A13,$A$5:$A$8,0),MATCH(LOOKUP("zzz",H$13:H13),$B$2:$C$2,0)))

or,

=IF(A13="","",INDEX($B$5:$C$8,MATCH(A13,$A$5:$A$8,0),SUMPRODUCT(($B$3:$C$4=E13)*COLUMN($A1:$B1))))

Regards
Bosco

 

[Tripp]

Good suggestion,

I might see if I can determine which is fastest

Cheers