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/

  • 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


    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!