# Excel -- need lookup or match function

Experts:

I need some assistance with a basic lookup or match function.

Please see attached XLS which includes details and the requirement for the lookup/match function.

EEH
Lookup-or-Match-Function.xlsx
Professor J

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

ProfessorJimJam:

Thank you... PERFECT solution!  I appreciate your help on this.

EEH
=INDEX(A:A,MATCH("*",OFFSET(\$B\$1,IF(ROW()=1,0,MATCH(OFFSET(F1,-1,0),A:A,0)),0,10000),0)+IF(ROW()=1,0,MATCH(OFFSET(F1,-1,0),A:A,0)))
Professor J

you are most welcome.

also if your excel version is 2010 and above then you can use the aggregate function to acheive the same result

like this  and this will not require the use of key stroke ( Control Shift Enter)
it can work with simply pressing (Enter) only.
``=INDEX(\$A\$1:\$A\$11,AGGREGATE(15,6,(ROW(\$B\$1:\$B\$11)-MIN(ROW(\$B\$1:\$B\$11))+1)/("x"=\$B\$1:\$B\$11),ROW(1:1)))``
Lookup-or-Match-Function.xlsx