Multi criteria lookup

[Samosa]

Hello, I have a data set from A1:H2 and seeking solution in I1 and I2 from Reference list in A7:B15.

For Bob, I need to lookup the values in A1:G1 against the reference list and return the value corresponding to the last nonblank instance.

Thanks in advance!

A B C D E F G H I 1 A B C D E F Bob dark 2 A B1 C2 D2 Mike cake 3 4 5 6 REFERENCE LIST 7 A 8 B 9 C car 10 D dark 11 E 12 F 13 B1 14 C2 cake 15 D2 <colgroup><col width="64" style="width: 48pt;" span="10"> <tbody> </tbody>

<tbody>
</tbody>

 

[Scott T]

In I1 Try

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula. If no match is found it will return "no value found" change this to what you want and copy down

=IFERROR(INDEX($B$7:$B$15,AGGREGATE(14,6,MATCH($A1:$F1&FALSE,$A$7:$A$15&ISBLANK($B$7:$B$15),0),1)),"no value found")

 

[Aladin Akyurek]

Also, in I1 control+shift+enter, not just enter, and copy down:

=LOOKUP(REPT("z",255),IF(ISNUMBER(MATCH($A$7:$A$15,A1:F1,0)),$B$7:$B$15))

 

[Samosa]

Thanks Scott T!!