Why VLOOKUP not working in Excel – Top 5 Problems with Solutions

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.

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 –

download excel workbookVLOOKUP-NOT-WORKING.xlsx

 

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.

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.

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

 

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.

=VLOOKUP(G1,A2:D32,2)

Using an approximate match instead of an exact match is the underlying reason for 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(G1,A2:D32,2,0)

 

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(H1,B2:E32,2,0)

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(H1,$A$2:$D$32,2,0)

 

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.

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.

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.

=INDEX(B2:D32,MATCH(G1,D2:D32,0),1)

 

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.

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.

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.