# vlookup with multiple condition

• hey everyone,

i am working on a sheet using vlookup to find matching gender and shoe size with socks size. however i can only get the formula to work under the reference table. if the size is beyond or under the given size, it suppose to display too large or too small. i don't know how to add that condition to the formula, would you guys please help me. thank you.

i used =IF(C20="M",VLOOKUP(B20,mensocksize,2,TRUE),VLOOKUP(B20,womensocksize,2,TRUE)). i need a formula, not only look up the matching size, but also solve D20 and D21 as too big or too small.

Thank you so much. forum.ozgrid.com/index.php?attachment/38455/

## Files

• Re: vlookup with multiple condition

Assuming anything over size 13 should be too large, then:
=IF(B13<MIN(IF(C13="M",mensocksize,womensocksize)),"Too small",IF(B13>MAX(IF(C13="M",mensocksize,womensocksize)),"Too large",IF(C13="M",VLOOKUP(B13,mensocksize,2,TRUE),VLOOKUP(B13,womensocksize,2,TRUE))))
in D13.

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

• Re: vlookup with multiple condition

Quote from rory;556689

Assuming anything over size 13 should be too large, then:
=IF(B13<MIN(IF(C13="M",mensocksize,womensocksize)),"Too small",IF(B13>MAX(IF(C13="M",mensocksize,womensocksize)),"Too large",IF(C13="M",VLOOKUP(B13,mensocksize,2,TRUE),VLOOKUP(B13,womensocksize,2,TRUE))))
in D13.

this formula works perfectly fine. Thank you very much. however i could not understand the logic. would you please explain why is it written in this way. im a bit confused how it works.

• Re: vlookup with multiple condition

i looked into it. i think i understood what you meant!!! i am sooo excited.. thank you very very much!!!!

• Re: vlookup with multiple condition

Glad to help. You could simplify that formula if you added a couple of rows to the tables (0 = Too small, and 14 - Too big)

Rory
Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!