Solutions for VLOOKUP not working or showing #N/A

Major reasons for your VLOOKUP not working properly or showing error (#N/A or #REF!) could be:

1. The Lookup values have Trailing space at the end
2. The Numbers entered have been formatted as Text
3. You forgot to reference lock your table array
4. The file or table array to lookup has been deleted

So, let's tackle these issues and get solution for each of them.

1. Lookup values having trailing space at the end

Let's look at the below VLOOKUP example. Although, the values are available in the source table, the lookup table is not returning values for some of them. The problem is not directly evident. To see the problem, go to the lookup value in the source table and click the cursor at the very end of value. The extra space will be evident at the end of the input.

Solution: It is very simple. Please remove the extra space by pressing "Backspace" in edit mode.

2. The numbers entered have been formatted as Text (shows #N/A error)

In case none of the lookup value are not returning results (#N/A is appearing), it may be possible that the formatting of source is incorrect (the numbers will appear to be aligned left).  Click on the lookup reference of the data set and check in the formula bar if there is an apostrophe before your entry.

This is sometimes intentionally done to populate leading zero in the number (although a better way is through custom formatting...!!).

Solution: Again, the solution is very simple. Multiply all your lookup values by 1. This will convert all of them into number format (the numbers will get automatically aligned right).

3. You forgot to reference lock your table array

This is very common problem that occurs when we drag the VLOOKUP formula after entering it in one cell. Often we forget to lock the table array and as a result the lookup values you are trying to match are no longer part of original lookup array (since the lookup array also shifts down when we drag it).

Solution: Before dragging the VLOOKUP formula, press F4 key to lock lookup array (in edit mode)

4. The file or table array to lookup has been deleted (shows #REF! error)

Sometimes the source data used for VLOOKUP function gets deleted or sheets are improperly moved without reference. In such case VLOOKUP shows #REF! error.

Solution: Try and locate the source data and verify the linking of VLOOKUP formula again