VLOOKUP against multiple criteria

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
I am doing a standard VLOOKUP across worksheets, but some of the "lookup values" & "table array" values are exactly the same with the size column (5) being the defining difference.

=VLOOKUP($A$2:$A$1608,POS_Inventory!$A$1:$E$9867,2,FALSE)

Any way to return value in column2 only if "lookup value" & columnE match?

thanks in advance
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
I am doing a standard VLOOKUP across worksheets, but some of the "lookup values" & "table array" values are exactly the same with the size column (5) being the defining difference.

=VLOOKUP($A$2:$A$1608,POS_Inventory!$A$1:$E$9867,2,FALSE)

Any way to return value in column2 only if "lookup value" & columnE match?

thanks in advance

Do you mean something like...

=INDEX(POS_Inventory!$B$1:$B$9867,MATCH($A2,POS_Inventory!$E$1:$E$9867,0))

which must be copied down?
 

gardnertoo

Well-known Member
Joined
Jul 24, 2007
Messages
938
You could build a helper column which concatenates the two values you want to use as the multiple criteria. So instead of finding multiple "Shirt" entries and having to determine which one is "XL", your list would have "Shirt S", "Shirt M" etc and the INDEX MATCH function would find the "Shirt XL" entry.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

No luck with that formula...but I will play with it to see if it can work.

Try to formulate your question completely in words like in "If A2 is in F2:F4, then return the text value from N2:N4"...
 

MichaelWayne_71

Board Regular
Joined
Nov 9, 2005
Messages
60
Try to formulate your question completely in words like in "If A2 is in F2:F4, then return the text value from N2:N4"...

Find A2 in Pos_Inventory!A2:E9xxx and return Pos_Inventory!B if D & Pos_Inventory!E match

Hope I explained that correctly! Thanks for the help;)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Find A2 in Pos_Inventory!A2:E9xxx and return Pos_Inventory!B if D & Pos_Inventory!E match

Hope I explained that correctly! Thanks for the help;)

Are you saying that if A2 is in Pos_Inventory!A2:A9867 and D2 in Pos_Inventory!E2:E9867, return a corresponding value from Pos_Inventory!B2:B9867?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
That looks perfect...am I over-thinking this?

I guess you mean yes regarding my last question. If so:

Control+shift+enter, not just enter...

Are you saying that if A2 is in Pos_Inventory!A2:A9867 and D2 in Pos_Inventory!E2:E9867, return a corresponding value from Pos_Inventory!B2:B9867?
Code:
=INDEX(Pos_Inventory!$B$2:$B$9867
   MATCH(1,IF(Pos_Inventory!$A$2:$A$9867=A2,
                IF(Pos_Inventory!$E$2:$E$9867=D2,1)),0))

If you need many such formulas, you might run into a performance problem. Report back if this is the case and we can set up an approach using concatenation.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top