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

How to use VLOOKUP in Excel 2013

VLOOKUP is probably one of the most useful function in Excel when it comes to looking up fields related to a variable. We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

The V in VLOOKUP stands for Vertical i.e. it looks up in vertical table. Since most of the real world data appears in vertical table, the VLOOKUP is one of the most used function in Excel. It works similar to human mind, first it searches for relevant key item and goes further in the row to get it's details.

Let's understand this better by most commonly encountered real world example of Employee details. Suppose a company has 10 employees with unique Employee ID followed by details like Name, Departement, Contact Number and E-mail ID.

If we need the Contact Number corresponding to Employee ID 201309 we first search the row in which this ID exists and then we move further in the same row to locate Contact Number. That's exactly how Excel also works, we just need to feed the data for what we are looking and from where we need to look.

Syntax:      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Insert the value to search in the first column of range or table. This value could be a value or it could be a cell reference (more dynamic). This helps excel to identify the row in which our data exists.
• table_array: The entire range of cells that contains data. We can directly use the range reference or a range name. This tells excel the location for search.
• col_index_num: The column in range selected under table_array corresponding to desired matching value field. (eg. Contact Number lies in column 4, hence 4 should be inserted)
• range_lookup: Optional field. Specifies whether we need exact match or approximate match. FALSE (or 0) corresponds to exact match while TRUE (or 1) corresponds to approximate match. The default value is FALSE (or 0) i.e. exact match.

IMPORTANT NOTES:

• If col_index_num < 1, Excel gives #VALUE! error
• If col_index_num > number of columns in range, Excel gives #REF! error
• While searching for text values in the first column, ensure that any cell value does not contain leading spaces, trailing spaces or inconsistent quotation marks. It may give error or incorrect values in the output.
• While searching for number or date values in the first column, ensure that data is stored in appropriate format (not as text format)