How to fix VLOOKUP error in Excel

VLOOKUP error makes many Microsoft Excel users sweat every time they think of it. If you're not too familiar with Excel, VLOOKUP is one of the most difficult functions.

The purpose of VLOOKUP is to search and return data from another column in an Excel spreadsheet. Unfortunately, if you give the wrong VLOOKUP formula, Excel will return the error to you. Let's look at some common VLOOKUP errors and explain how to fix them.

Error # N / A

One of the most common VLOOKUP errors in Excel is the # N / A error. This error occurs when VLOOKUP cannot find the value you are looking for.

How to fix VLOOKUP error in Excel Picture 1 — Error # N / A

The search value may not exist in the data range or you may have used the wrong value. If you see an N / A error, double-check the value in the VLOOKUP formula.

If the value is correct, then your search value does not exist. This assumes you use VLOOKUP to find exact matches, with the range_lookup argument set to FALSE.

If the range_lookup argument at the end of the VLOOKUP formula is missing or set to TRUE, VLOOKUP will return the # N / A error if your data range is not sorted in ascending order.

It will also return the # N / A error if the search value is less than the lowest value in the range.

Other common reasons for # N / A errors include using the search column that is not the furthest left column and using cell references for search values containing numbers but formatted as text or contains redundant characters such as spaces.

Error #VALUE

The #VALUE error is often an indication that the formula containing the VLOOKUP function is incorrect in some way.

How to fix VLOOKUP error in Excel Picture 2 — Error #VALUE

In most cases, this is usually due to the cell you are referencing as the search value. The maximum size of a VLOOKUP lookup value is 255 characters.

If you handle cells containing longer strings of characters, VLOOKUP will not be able to handle them.

The only workaround for this is to replace the VLOOKUP formula with the combined INDEX and MATCH formula.

This error will also appear if you have used an incorrect reference to the cells in your formula, especially if you are using data ranges from another workbook.

The workbook reference needs to be enclosed in square brackets for the formula to work correctly.

If you get the #VALUE error, double-check the VLOOKUP formula to verify that cell references are correct.

Error #NAME

If your VLOOKUP error is not #VALUE or # N / A, it could be a #NAME error. Do not panic! This is the easiest VLOOKUP error to fix.

How to fix VLOOKUP error in Excel Picture 3 — Error #NAME

A #NAME error appears when you misspell a function in Excel, whether it's VLOOKUP or another function like SUM. Click on your VLOOKUP box and double-check that you really spelled VLOOKUP correctly.

If there are no other problems, your VLOOKUP formula will work when this error is fixed.

Functions like VLOOKUP will change your life. At the very least, it will turn Excel into a more powerful tool for data analysis.

If VLOOKUP is not right for you, take advantage of these top Excel functions instead.