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.

Participate now!

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

Register Yourself Login