Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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
0
ExpExchHelp
Asked:
ExpExchHelp
  • 2
1 Solution
 
ProfessorJimJamCommented:
put this formula in F and drag down

formula to be entered with control shift enter.

=INDEX($A$1:$A$11,SMALL(IF("x"=$B$1:$B$11,ROW($B$1:$B$11)-MIN(ROW($B$1:$B$11))+1),ROW(1:1)))

see attached file.  this is just an example. you can change the range as per your neeed.
Lookup-or-Match-Function.xlsx
0
 
ExpExchHelpAuthor Commented:
ProfessorJimJam:

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

EEH
0
 
Saqib Husain, SyedEngineerCommented:
=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)))
0
 
ProfessorJimJamCommented:
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now