Link to home
Start Free TrialLog in
Avatar of ExpExchHelp
ExpExchHelpFlag for United States of America

asked on

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.

Thank you in advance,
EEH
Lookup-or-Match-Function.xlsx
ASKER CERTIFIED SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ExpExchHelp

ASKER

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)))
Avatar of Professor J
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)))

Open in new window

Lookup-or-Match-Function.xlsx