Why VLOOKUP not working in Excel - Top 5 Problems with Solutions | MyExcelOnline

VLOOKUP is one of the most fundamental functions used in Excel to retrieve data from different tables within a spreadsheet. However, users occasionally face situations where they are unable to get the desired result from this function. Due to its widespread usage, encountering errors is also very common.

Watch our free training video on Why VLOOKUP not working in Excel:

In this article, we will explore the top 5 reasons why VLOOKUP not working and provide practical solutions to overcome these obstacles –

Before we move forward, Click here to understand the basics of how to use VLOOKUP in Excel.

Download the Excel Workbook below to follow along and understand why VLOOKUP not working in Excel –



1 – Incorrect Data Type

The lookup value and the value in the data table are of different data types. VLOOKUP not only matches the values but also considers their data types when searching in the data table. Therefore, it is important that both values share the same data type.

In this example, we can see that the lookup value is a formatted as number while the value in the data table is actually stored as text. Since VLOOKUP performs an exact match, the function will not locate a match in a column that contains text when the lookup value is in numerical form.

VLOOKUP not working

This is the underlying reason for VLOOKUP not working as intended.

Solution – Adjust the data types to match the lookup value and the value in the data table before performing the VLOOKUP operation.

Select all the values in the columns > Right-click on the cell > Select Convert to Number.

VLOOKUP not working

The VLOOKUP function will now yield the desired result without any error.

VLOOKUP not working


2 – Exact vs Approximate Match

The fourth argument of the VLOOKUP function is used to tell Excel whether we are looking for an exact match or an approximate match. This is an optional argument so if we keep it empty, Excel will assume that we are looking for an approximate match.

So, if we want an exact match but we forget to enter the fourth argument, Excel will assume an Approximate match and provide the results. In this example, we are trying to get the first name of the employee with employee ID 1020.


Using an approximate match instead of an exact match is the underlying reason for VLOOKUP not working.

VLOOKUP not working

Solution – If we want an exact match, please make sure to enter FALSE or 0 as the fourth argument. Use this formula –


VLOOKUP not working


3. Incorrect Cell Reference

Sometimes, when copying the formula across different cells, Excel may adjust the references incorrectly, resulting in errors. In this example, when we copy the formula from cell G2 to cell H2, the table reference gets changed from A2:D32 to B2:E32 leading to incorrect results.


VLOOKUP not working

Not locking the table reference is the reason for VLOOKUP not working as intended.

Solution – To avoid such unintentional alterations, it is essential to lock the table reference by pressing the F4 key. This action will prevent any undesired changes and ensure the formula is frozen when copied to different cells.


VLOOKUP not working


4 – Resultant column in VLOOKUP is on the left

The VLOOKUP formula searches for a value in the first column of an array and returns a value to the right of that array. However, issues can arise when the table is structured in a way where the resultant column appears on the left of the lookup column. VLOOKUP will not be able to provide results in such situations.

In this example, we are trying to search employee’s first name based on the date of joining. The resultant column i.e. First Name is on the left of the lookup column i.e. DOJ.

VLOOKUP not working

The resultant column being on the left of the lookup column is the reason for VLOOKUP not working as intended.

Solution 1 – We can rearrange the table, ensuring that the resultant column is positioned to the right of the lookup column. This adjustment will allow VLOOKUP to function accurately.

VLOOKUP not working

Solution 2 – We can utilize the INDEX MATCH functions to retrieve the desired result without modifying the table’s layout. The INDEX MATCH combination offers more flexibility and can handle situations where VLOOKUP may fall short.


VLOOKUP not working


5. Duplicate Values

VLOOKUP only returns the first record that matches the value we are searching for. If we have multiple records, VLOOKUP will not be able to extract all of those.

In this example, we are multiple employees joining on the same date. If we want to extract the name of the employee using VLOOLKUP, it will only provide us with the first matching record.

VLOOKUP not working

Having duplicate lookup values in the data table is the reason for VLOOKUP not working as intended.

Solution – We can use a Pivot Table to extract all the employee names that have joined on the exact dates.

VLOOKUP not working

The article explores common issues faced with VLOOKUP in Excel and provides solutions for why VLOOKUP not working. These include dealing with incorrect data types, ensuring exact matches, avoiding incorrect cell references, handling situations where the resultant column is on the left, and resolving limitations with duplicate values.

Click here for Microsoft’s article on how to correct a #N/A error in the VLOOKUP function.


Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn