Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel -- need lookup or match function

Posted on 2016-10-06
4
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 27

Accepted Solution

by:
ProfessorJimJam earned 2000 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 27

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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