×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

How to

How can I perform a LOOKUP with MULTIPLE CRITERIA to return a SINGLE VALUE? by SkipVought
Posted: 25 Mar 14 (Edited 31 Mar 14)

We often find circumstances where multiple lookup criteria are required to return a single value.

The VLOOKUP() function is usually the go-to approch, but with multiple lookup values, it becomes more difficult.

Moreover, VLOOKUP() requires the lookup column to be to the left of the return range, and even if you used a concatenated key, you would have to modify your table in order to make it useable. This is the primary reason that I hardly ever use the VLOOKUP() function, in favor of INDEX() & MATCH() for single lookup ranges or INDEX & SUMPRODUCT()/ROW() for multiple lookup ranges as follows:

=INDEX(Return_Range, SUMPRODUCT((Lookup_Range1=Criteria1)*(Lookup_Range2=Criteria2)*(ROW(Return_Range)-MIN(ROW(Return_Range))+1)))

Of course, you can have as many Lookup/Criteria as you need.

Here is an example of a lookup with 3 criteria:

Source Table, using Named Ranges based on Names in the TOP ROW, and the table headings in ROW 2701 (for no particular reason other than it is other than row 1)

PART	ORDER_NO  	OPER	WORK_CNTR
widget	001000000841	0020	AZX00007
widget	001000000841	0030	WSD00WSD
widget	001000000841	0040	TGH16613
widget	001000000841	0050	GBN171F2
bibit	001000208851	0020	AZX00007
bibit	001000208851	0030	DEW16476
bibit	001000208851	0040	SAQ00081
bibit	001000208851	0050	WSD00WSD
 

And here are my Lookup Values starting in G2702 seeking to return the WORK_CENTER:

PART	ORDER_NO  	OPER
widget	001000000841	0040
bibit	001000208851	0030
 
And here is the formula in J2702

J2701: =INDEX(WORK_CNTR, SUMPRODUCT((PART=G2702)*(ORDER_NO=H2702)*(OPER=I2702)*(ROW(WORK_CNTR)-MIN(ROW(WORK_CNTR))+1)))

And my results:

PART	ORDER_NO  	OPER	WORK_CNTR
widget	001000000841	0040	TGH16613
bibit	001000208851	0030	DEW16476

 


BTW, this can only be used where there is ONE and ONLY ONE row for the combination of criteria[/b] (use the PivotTable wizard to verify that this is the case if you are unsure). Otherwise the multiple ROW() values get summed, resulting in an incorrect result!

If you ALWAYS have your table headings in ROW 1, then the formula can be simplified as:

=INDEX(Return_Range, SUMPRODUCT((Lookup_Range1=Criteria1)*(Lookup_Range2=Criteria2)*(ROW(Return_Range)-1))

Happy lookups!

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close