How to Fix Excel VLOOKUP Not Working [4 Cases]

VLOOKUP Not Working

VLOOKUP function is an important feature in Excel. According to Microsoft, if you need to find things in a table or a range by row, use VLOOKUP. For financial staff, this VLOOKUP may be one of the features that they will use frequently.

With this feature, they can easily look up a price of an automotive part by the part number, or find an employee name based on their employee ID. The VLOOKUP formula is as follows:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

An example of this formula is as follows: =VLOOKUP(B13,A2:C10,2,FALSE). This formula means that it should scan column A (from the 2^nd row to the 10^th row) to find the cell that has the same value as B13. If it finds the matched cell, it will return the value of the 2^nd column (starting from column A). False means that it will find the exact value.

However, some people report that they have encountered the Excel VLOOKUP not working issue. When they execute the VLOOKUP function, they may get various errors. Why is my VLOOKUP not working? How to solve this problem? Keep reading and you will get the answer.

Quickly Fix: Excel Keeps Crashing in Windows 10

Sometimes, MS Excel keeps crashing in Windows 10 due to certain reasons. This article provides 5 methods that may help you resolve the problem effectively.

Read More

Case 1. #N/A Error

When you execute the VLOOKUP function, you may get the “#N/A” error. The causes and corresponding solutions of this error are as follows:

1. The lookup value is not in the first column of the array. When you use VLOOKUP, you will specify a searching range (an array). VLOOKUP can only look for values on the left-most column in the table array. To solve the “#N/A” error, you can change the search range.
2. The exact match is not found. This case usually occurs when you type False for an exact match. The VLOOKUP is unable to find an exact match in your data. In this case, you should check whether you have typed the correct lookup value.
3. The lookup value is smaller than the smallest value in the array. This case usually occurs when you type True for an approximate match. TRUE looks for an approximate match in the array and returns the closest value lesser than the lookup value. To solve this issue, you should correct the lookup value.
4. The lookup column is not sorted in ascending order. This case also occurs in an approximate match. To solve the problem, you can rearrange the column or use an exact match instead.
5. The value is a large floating-point number. Floating point numbers are numbers that follow after a decimal point. Excel cannot store numbers with very large floating points. To make the VLOOKUP function work correctly, the floating-point numbers will need to be rounded to 5 decimal places.

Quickly Fix Arrow Keys Not Working in Excel on Windows 10

Are you facing that pressing the arrow key is moving the entire spreadsheet instead of a single cell? Read the post and get the solutions to fix this issue.

Read More

Case 2. #VALUE Error

You will get the “#VALUE” error when you do the following things:

1. The column number of the returning value is less than 1. VLOOKUP can’t look to its left. It will look down the leftmost column of a table and return information from the right. Therefore, the column number of the returning value should be no less than 1.
2. The workbook path is incorrect or incomplete. VLOOKUP allows you to look up data from another workbook. To do that, you should use this formula: =VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE). Please make sure the path of the workbook is complete.
3. The lookup value exceeds the limit of 255 characters. You should shorten it.

Case 3. #REF Error

You will get the “#REF” error if the column number of the returning value is greater than the number of columns in the search array. In this case, you should correct the column number.

Case 4. VLOOKUP Returns Incorrect Results

One of Excel VLOOKUP not working cases is that the VLOOKUP returns incorrect results. This issue usually occurs because:

1. You have changed the table (insert or delete columns/rows) while the VLOOKUP formula is not changed accordingly.
2. Your table contains duplicates. The VLOOKUP function only returns the first record that matches the value you looked for. If your table contains duplicates the VLOOKUP may return incorrect results.

How to Fix Filter Not Working in Excel [3 Cases]

This post introduces 3 cases where the Filter feature doesn’t work and tells you how to solve this issue.

Read More