Pinterest Pixel

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

John Michaloudis
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.

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.

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

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.

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

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

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

 

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.

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

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)

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

 

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)

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

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)

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

 

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.

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

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.

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

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)

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

 

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.

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

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.

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

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.

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.

See also  7 Easy Steps to Master INDEX MATCH with Multiple Criteria

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...