Question: Why is my VLOOKUP formula returning #N/A when I know the value is in the lookup table?

By | October 12, 2015

The problem

There are a few different ways this can happen:

1. The lookup table might be formatted differently from the SearchFor value. For example there might be values that look like numbers but their containing cells are formatted as text

2. There might be spaces around the value in the lookup table

The solutions

1. First convert the SearchFor value to a string and then to a number to see if formatting is your issue.

To a string

=VLOOKUP ( SearchFor & “” , WhereToSearch , WhichColumn , NearOrExact )

To a number

=VLOOKUP ( SearchFor + 0, WhereToSearch , WhichColumn , NearOrExact )

So in the first instance you’re adding no letters to the string (inverted commas with nothing between them) to force excel to recognise SearchFor as a string and in the second instance you’re adding zero to force it to be recognised as a number. Test each and if one works, that’s your formula to drag down if you need to.

2. You can find out if this is the issue by clicking into both cells to see that they aren’t identical, so for example the value you’re looking up is 1234 but the lookup table has 1234 with a space after it (“1234 “)

If you don’t need any spaces you can simply do a find and replace – find ” ” (space) and replace with nothing

Or you can “trim” the values in the column by, in an empty column out to the right, typing in, if the value is in cell A2 for example,  =TRIM(A2). Copy this formula all the way down and then copy and paste the new column (paste special > values) into the old one. The trim function removes rogue spaces from either side of the string.

Want to get good at using the VLOOKUP function?

Download our practical VLOOKUP tutorial workbook with step-by-step instructions and examples to help you become a VLOOKUP pro.

Leave a Reply

Your email address will not be published. Required fields are marked *