vlookup #n/a error when values exist

  • Hello,


    I am running a vlookup and for around 2500 values out of a total 8100 values I am getting #N/A error. The values for these do exist, so I am not sure what is causing the error. I used the trim function to remove any spaces. Please help. I am uploading the file with values. I am looking for the vlookup to return amount values from sheet 2 by looking at the vendor numbers. As you can see, almost 2500 amounts are not returned. Thanks.


    Regards,
    AK

  • Re: vlookup #n/a error when values exist


    Your lookup range needs to made absolute by adding the $ symbols before the row/column indentifiers.


    Also, your lookup value needs to be a single cell, made relative by not adding the $ in front of the row number.


    When you copy the formula down the lookup value will increment, but the range lookup in the other sheet will be consistent.


    Try:


    [COLOR="#0000FF"]=VLOOKUP($A2,Sheet2!$B$2:$C$8575,2,FALSE)[/COLOR]


    copied down

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: vlookup #n/a error when values exist


    You need to set the VLOOKUP lookup array to absolute reference.


    Change your formula in Sheet1 B2 to


    =VLOOKUP($A2,Sheet2!$B$2:$C$8575,2,FALSE)

    and copy down.


    This will still returne one #NA, for Vendor 3058514, that is because Vendor 3058514 does not exist on Sheet2.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: vlookup #n/a error when values exist


    You're welcome.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.