Forum Discussion

ntscooby's avatar
ntscooby
Copper Contributor
May 07, 2019
Solved

Problem with VLOOKUP

Hey all! I have this formula that makes certain that the value in column G is set to "Confirmed", then if it is, I have it Vlookup  as so: =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,$L$2:$L$7),"N/A"). So, here in lies my problem... if works for some of the categories in column B, but not others, as it returns #value.

 

Can anyone help me figure it out?

excel
Formulas and Functions
  • Wyn Hopkins's avatar
    Wyn Hopkins
    May 08, 2019

    Hi ntscooby 

     

    =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,$L$2:$L$7),"N/A"

     

    The Bold part needs to be replaced by FALSE

     

    =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,FALSE),"N/A"

5 Replies

  • Rich99's avatar
    Rich99
    Iron Contributor
    May 08, 2019

    ntscooby  Hi,  The last part of a VLOOKUP formula is usually a TRUE or FALSE value depending on whether an exact match is required or not. Remove the reference to $L$2:$L$7 and replace with 0 or 1 depending on your requirements and see what happens.

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    May 08, 2019

    Hi ntscooby 

     

    =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,$L$2:$L$7),"N/A"

     

    The Bold part needs to be replaced by FALSE

     

    =IF($G6="Confirmed",VLOOKUP($B6,$J$2:$N$7,3,FALSE),"N/A"