Solved

# Excel -- need lookup or match function

Posted on 2016-10-06
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
Question by:ExpExchHelp
• 2
4 Comments

LVL 25

Accepted Solution

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

ID: 41831582
ProfessorJimJam:

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

EEH
0

LVL 43

Expert Comment

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

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)))
``````
Lookup-or-Match-Function.xlsx
0

## Featured Post

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.