VLOOKUP against multiple criteria

[MichaelWayne_71]

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

 

[Aladin Akyurek]

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]

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.

 

[MichaelWayne_71]

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

 

[Aladin Akyurek]

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]

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]

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?

 

[MichaelWayne_71]

That looks perfect...am I over-thinking this?

 

[shemayisroel]

That looks perfect...am I over-thinking this?

Look like you may be

I'd suggest you take a look at INDEX/MATCH and see if thsi would help

http://www.contextures.com/xlFunctions03.html

This was Aladins solution

HTH

 

[Aladin Akyurek]

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?

=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.