Solved

Excel -- need lookup or match function

Posted on 2016-10-06
4
52 Views
Last Modified: 2016-10-11
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
Comment
Question by:ExpExchHelp
  • 2
4 Comments
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41831569
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
 

Author Comment

by:ExpExchHelp
ID: 41831582
ProfessorJimJam:

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

EEH
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 41831585
=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
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41831593
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

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question