VLOOKUP Troubleshooting in Excel

Using the VLOOKUP function is a fast and easy way to find information in Excel when the data is organized in columns. It is however easy to make a mistake while writing a VLOOKUP formula. This page describes the most frequent errors you could face and how to troubleshoot them.

#N/A error

VLOOKUP #N/A error

A value is not available to the formula or function

There is a typo in the Lookup value

Typos are a simple and common reason. Check that the Lookup value is spelled correctly.

Approximate match is incorrectly configured

If you are looking for an approximate match (TRUE), the VLOOKUP function searches for the value closest to and inferior to the value you are looking for. The #N/A error can appear in two cases:

  • The lookup value is smaller than the smallest value in the lookup array.
  • The lookup column is not sorted in ascending order.

For more information, see approximate match.

The Lookup value doesn't exist

If you are searching for an exact match (FALSE), and the Lookup value doesn't exist in the first column of the Search range, #N/A will be returned.

For more information, see exact match.
You can replace N/A results by an empty or user-friendly value with the IFNA formula.

A VLOOKUP parameter is missing

If you get the #N/A error, check if a parameter is missing within the VLOOKUP function. VLOOKUP works with a minimum of three parameters (the Lookup value, Search range, and Column number are required).

Wrong data format

You can get the #N/A error if you have imported data from an external database. The number format of the Lookup value must match the format of the matching value in the Search range. Also, make sure there are no hidden apostrophes before the number values.

Hidden spaces or non-printing characters

If you get the #N/A error, there may be extra spaces in the data range. You can use the TRIM function to remove hidden spaces. Apply TRIM to the Lookup value. For example =VLOOKUP(TRIM(G4), B7:D12, 3, FALSE).

Path to the Search range is not provided

This error occurs if VLOOKUP is not able to find the Search range.

  • Check for typo in the Search range
  • If the Search range is in a different sheet of the Lookup value, you must specify the sheet's name followed by the exclamation mark. For example, if the Search range B4:D7 is located in a sheet namedSheet2, the parameter is Sheet2!B4:D7
  • If the Search range is in a different spreadsheet, you must enclose the spreadsheet's name, including the extension in [square brackets], and specify the sheet's name followed by the exclamation mark. For example, if the Search range is located in a spreadsheet named Data range, the parameter is'[Data range.xls]Sheet1'!B4:D7

For more information, see how to use VLOOKUP from another sheet.

Path to the Search range is incorrect

If you get the #N/A error, and the Search range is in another sheet, it's maybe because VLOOKUP cannot find the name of the sheet you provided. Make sure to specify the correct path.

For more information, see how to use VLOOKUP from another sheet.

#VALUE! error

VLOOKUP #VALUE! error

A value used in the formula is of the wrong data type

Lookup value exceeds 255 characters

You get a #VALUE error because the Lookup value exceeds the limit of 255 characters. Consider shortening the value or using a combination of INDEX and MATCH functions.

The Column number is not found

If you get this error, make sure that the Column number is at least 1. The reason is that column indexing of a range starts at 1.

Typos or mathematical impossibilities

This error means that Excel can't interpret the VLOOKUP function. Make sure the formula is correctly written.

For more information, see how to use VLOOKUP.

#REF! error

VLOOKUP #REF! error

Moving or deleting cells caused an invalid cell reference, or function is returning reference error

The Column number is greater than expected

If you get this error, make sure the Column number you specify isn’t greater than the number of columns in the Search range.

For example, if the Search Range is B7:D9, the Column number can be either 1 (column B), 2 (column C), or 3 (column D).

#NAME? error

VLOOKUP #NAME! error

The formula contains unrecognized text

Typo in the function name

If you get the #NAME error, Excel cannot read the VLOOKUP function. It's probably because there is a typo in the function name.

Can't find the range

If you are using a named range and you see the #NAME error, check if:

  • There is no typo in the name
  • The named range is correctly defined in the spreadsheet

#SPILL! error

VLOOKUP #SPILL! error

We can't spill beyond the edges of the worksheet

Result data spill over to non-empty cells

This error can occur if you use VLOOKUP with arrays and return multiple results. Make sure that there are enough empty cells below and next to the formula's cell.

General VLOOKUP tips

  • The Lookup value must be in the first column of the Search range
  • The format of the Lookup value must match the format of the matching value in the Search range
  • Make sure there is no hidden space or apostrophes in the first column of the Search range
  • If you are using text in the formula, add quotation marks around that text
  • Add commas between each parameter
  • If you drag down the formula, lock the search range (with $)
  • Add the name of the sheet followed by an exclamation mark if the Search range is referenced in another sheet
  • Make sure to point to the correct column in the Search range
  • Use the correct Approximate match parameter value
  • If you use TRUE as an approximate match (or don't specify one), make sure the values in the first column of the Search range are sorted in ascending order
  • If you use FALSE as an approximate match, make sure the Lookup value is in the first column of the Search range