Why VLOOKUP is Not Working in Excel? How to Fix it

Spreadsheets are widely used across the corporate world to manage lists of data or perform calculations on numbers. Similarly, workbook programs come with many functions and formulas to help users manage and find information from large amounts of data. VLOOKUP is one such function in Excel that usually works well to find data but often fails to return the correct result or shows errors.

There are different reasons this matching formula might not work properly, such as extra spaces or missing values. Since this can cause issues when managing large amounts of important business data, resolving it is very important. So, this article will discuss the VLOOKUP Excel not working problem and the solutions to resolve this issue comprehensively.

Part 1. Understanding VLOOKUP in Excel? Definition & Examples

VLOOKUP is used to search for a value in the first column of a table and return a related value from another column in workbooks. It stands for Vertical Lookup and works best in lists where the search value is placed in the leftmost column. While it is used to match items like names and product codes across tables, users often face issues that cause the Excel lookup not working.

You can understand the usage and importance of this lookup feature in workbooks once you go through a few examples of using it. Therefore, we have shared a few examples below where this function is used to help you understand why it is a big problem when VLOOKUP is not working in Excel:

Example 1. VLOOKUP from Another Sheet

Users can apply this formula to search for value in a different worksheet instead of the current one. Consequently, they can adjust the formula and enter the sheet name followed by an exclamation mark before the cell range to find the desired entry.

Example 2. VLOOKUP from Another Workbook

The formula also works to pull data from a separate file by including the workbook and sheet name in the formula. You just need to ensure the other workbook is open and enter its name by enclosing it in square brackets before the worksheet’s name.

Example 3. VLOOKUP from a Named Range

A name range is a label given to a group of cells that you plan to reuse regularly or multiple times. You can use named ranges in VLOOKUP, as it makes the formulas shorter, and you don’t have to update the cell references if the sheet structure changes.

Example 4. VLOOKUP from a Closed Workbook

Even if the source file is closed, the lookup formula keeps working by showing the full path to the file. Hence, you won’t need to reopen the file each time you use the sheet, which makes it ideal for referencing past records.

Example 5. VLOOKUP from an Excel Table

You can reference an Excel table directly by its name using the VLOOKUP formula instead of using fixed cell ranges. Since the formula changes automatically when you add or remove rows from the table, it is reliable for managing growing data sets.

Part 2. 8 Reasons Why VLOOKUP Excel Not Working & Their Solutions

When any issue with the VLOOKUP occurs, you won’t be able to look for data and values in huge workbooks. The following sections discuss a few common reasons VLOOKUP isn’t working in Excel and their common solutions:

1. Mismatched Data Types

The range_lookup argument, which is the last part of the formula, decides if the function returns an approximate or exact match. Therefore, the program searches for the nearest value that is less than or equal to the lookup value when the argument is left empty or set to TRUE. While using this argument, users get the VLOOKUP not working in Excel when their data is not sorted in ascending order.

Solution: You can solve this issue by always setting the fourth argument of the VLOOKUP formula to false for exact matches. Similarly, avoid leaving the argument blank unless you are using sorted data and want approximate matches.

2. Incorrect Table Array

Many users also face this error when the range provided to the VLOOKUP function does not fully include the lookup and the return columns. The program cannot find the correct row or column to return a value from when the selected range is too narrow. This usually leads to incorrect values or a VLOOKUP formula not working in Excel error.

Solution: Always double-check that the first column in the range includes your lookup values. Similarly, ensure the return column is inside the selected range to get a correct result in the end.

3. Extra Spaces in Data

Even one small invisible space can stop the workbook program from matching values with the VLOOKUP function. Many times, the data copied from other systems or typed manually includes leading or trailing spaces that go unnoticed. These extra characters make the program think the values are different and cause VLOOKUP Excel not working error or a #N/A result in the end.

Solution: You need to press “CTRL + F” simultaneously and enter a “Replace” tab. Following that, enter a space in the “Find What” dialogue box and leave the “Replace With” box empty. In the end, hitting the “Replace All” will remove the unnecessary spaces from the sheet.

4. A Column Has Been Inserted

You might break the formula or shift the positions of the columns by inserting an additional column into your table. Since the formula depends on a specific column number, inserting a new column affects that index and points to the wrong column. Mostly, the VLOOKUP is not working in Excel error due to this reason occurs when the team updates shared files or adds new columns during data updates.

Solution: A simple way out of this issue is to ensure no other person can edit the sheet and insert the columns. Yet, you must update the column index number in your VLOOKUP formula if someone has added the column already. Otherwise, use the MATCH() function in the formula in the following manner to solve the issue.

5. Table Contains Duplicates

VLOOKUP always returns the first match it finds and ignores any duplicates that follow. Consequently, if your table has duplicate values in the first column, this might result in displaying outdated data. Similarly, it's a serious issue in inventory or payroll, where duplicate entries usually exist. Since the function does not show errors, users may not realize that the VLOOKUP formula is not working in Excel.

Solution: Instead of VLOOKUP, use a PivotTable to find the desired results when duplicates are needed in the sheet. When duplicates are unnecessary, simply remove them by going to the “Data” tab and selecting the “Remove Duplicates” option.

6. The Table Has Got Bigger

Many times, your data range increases, but the VLOOKUP table_array argument doesn't include the newly added rows. The tool will not look beyond the defined area if the formula's range remains static and will miss new entries. This usually happens when data is appended, but the formula is not updated afterward. Similarly, it results in outdated outputs and VLOOKUP not working in Excel issues in bigger files.

Solution: You need to regularly review and update fixed ranges in formulas to include new data. Other than that, convert the targeted ranges into an official Excel table by selecting the entries, going to the "Home" tab, and selecting the "Format as Table” option. Afterward, choose any style and then use VLOOK again to find the relevant information.

7. Numbers are Formatted as Text

In many spreadsheets, numbers copied from external systems appear as text and do not match real numeric values. These text-format numbers won’t match true numbers during a lookup, even if they visually appear identical. As a result, the function returns #N/A even though the number clearly exists in the table. Usually, the VLOOKUP is not working in Excel error happens due to this reason when data is exported from PDFs.

Solution: This issue can be resolved by converting the format from text to numbers using these steps:

Step 1. Select the cell with the number in text form and right-click on it. Moving forward, you have to select the "Format Cells" option to make further changes.

Step 2. Moving forward, enter the “Numbers” tab, and upon choosing a format without leading zeros, hit the “Ok” button.

8. Lookup Value is Not Found

Since it simply can’t find the desired value, this is the most common and basic reason the VLOOKUP formula fails. While it usually happens due to a typo, you may face the VLOOKUP isn’t working in Excel problem due to a missing entry or looking in the wrong range. As a result, the software returns the #N/A error, which signals that no match was found in the data.

Solution: You need to ensure that you are searching in the correct column and double-check that the lookup value exists as entered.

Pro Tip. Regain Access to Corrupted Excel File with Crucial Formulas

Apart from the above issue, a malware attack can cause your file’s internal data to be corrupted. For that, you can use Repairit Excel File Repair Tool to solve and make your Excel sheet accessible with VLOOKUP enabled. Corrupt files’ data structure is parsed to repair the damaged part and allows the users to restore the pivot table, macros, and hidden sheets. Besides, it has a preview option, confirming the file is recovered successfully.

This tool recovers several files as it contains a batch repair feature, whether they got corrupted due to a power outage or malware. These advanced software programs can repair the damaged sections of the file without making changes to the internal data.

After knowing about the features of Repairit, you can now learn how it helps you repair damaged files as you resolve the Excel lookup not working:

Step 1. First of all, move to the “More Types Repair” section and press the “Document Repair" button.

Step 2. It will open a new window where you need to click the “Add” button to Import your damaged Excel files.

Step 3. Upon bringing the damaged Excel files inside the tool, press the “Repair” button at the bottom to start the process.

Step 4. To save the final repaired Excel files, press the “Save” button on the pop-up window and migrate the results.

Conclusion

To conclude, the error in VLOOKUP Excel not working arises due to many reasons, such as incorrect data type. Notably, it can be resolved by removing extra spaces, matching exact data types, and following other fixes discussed above. However, for corrupt files, you are advised to proceed with Repairit, which uniquely recovers the corrupt Excel files while protecting elements like pivot tables and formulas.