[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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