VLOOKUP #VALUE! Error: Causes and Fixes

A VLOOKUP formula returns #VALUE! most often when its column index argument is less than 1, or when the lookup value is longer than 255 characters. Check the formula’s arguments first: make the column index a positive number, keep it within the lookup range, and verify that the lookup value is valid. A column index that is too large usually produces #REF!, not #VALUE!.

What VLOOKUP Does and What Its Arguments Mean

VLOOKUP searches for a value in the leftmost column of a range and returns a value from another column in the same row. In Microsoft Excel for Microsoft 365 on Windows, its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The square brackets indicate that the final argument is optional; they are not typed into the formula.

Each argument has a distinct job:

lookup_value is the item to find, such as a product code in cell E2.
table_array is the range containing the lookup column and the return data. VLOOKUP searches only its leftmost column.
col_index_num is the position of the return column counted from the left edge of the selected range. The first column is 1, not 0.
range_lookup controls the match type. Use FALSE for an exact match. Use TRUE, or omit the argument, for an approximate match that generally requires the first column to be sorted.

For example, with product codes in A2:A10, names in B2:B10, prices in C2:C10, and the code to search for in E2, the formula is =VLOOKUP(E2,A2:C10,3,FALSE). The range starts at column A, so column C is the third column in that range. If E2 contains a code found in A2:A10, the formula returns the corresponding price from column C.

First Check: The Column Index Number

The most direct way to produce #VALUE! with an ordinary VLOOKUP is to use a column index below 1. A zero is a common mistake because worksheet columns are numbered from 1 in this argument; zero does not mean “the first column.” A negative value is invalid for the same reason.

With lookup values in A2:A10, descriptions in B2:B10, the search value in E2, and a return value needed from column B, this formula uses an invalid index and returns #VALUE!: =VLOOKUP(E2,A2:B10,0,FALSE). The corrected formula uses 2 because B is the second column of A2:B10: =VLOOKUP(E2,A2:B10,2,FALSE).

Count from the left edge of the selected range, not from the worksheet’s column letters. If the table range is C2:F20, then F is column 4 within that range. The number must also be numeric: a reference to a cell containing text such as “two” is not a valid column index. A number stored as text may also cause trouble depending on how the formula obtains it, so inspect the source cell and ensure the argument evaluates to a number.

When an Index Is Too Large, Expect a Different Error

A related misconception is that any bad column number causes #VALUE!. If the index is greater than the number of columns in table_array, VLOOKUP normally returns #REF!. For example, with a two-column range A2:B10 and a request for column 3, there is no third column inside the selected range.

With the lookup value in E2 and the table in A2:B10, this formula requests a nonexistent third column and returns #REF!: =VLOOKUP(E2,A2:B10,3,FALSE). Expand the range to include the intended return column, or correct the index to the position of a column that is actually included. This distinction helps narrow the diagnosis: zero or a negative index points toward #VALUE!; an index beyond the range points toward #REF!.

Check for Lookup Values Longer Than 255 Characters

Excel’s VLOOKUP has a 255-character limit for the lookup value. A long description, combined identifier, or pasted string exceeding that limit can cause #VALUE! even when the table range and column index appear sensible. The limit concerns the value being searched for, not the total number of characters in the entire worksheet.

With the key to search for in E2, keys in A2:A100, and corresponding results in B2:B100, this formula is a normal exact-match lookup: =VLOOKUP(E2,A2:B100,2,FALSE). If E2 contains a key longer than 255 characters, the formula may return #VALUE!. Check the key’s length and whether the lookup should use a shorter, unique identifier instead of a long text description.

For workbooks using Microsoft 365 or Excel 2021, XLOOKUP is an alternative worth considering because it separates the lookup and return ranges and does not require a numeric column index. With keys in A2:A100, results in B2:B100, and the search key in E2, use =XLOOKUP(E2,A2:A100,B2:B100,"Not found"). XLOOKUP can make the formula easier to maintain, but validate the behavior against your workbook’s data before replacing an established process. It is not available in some older Excel versions.

Distinguish #VALUE! from #N/A and Other Errors

Not every failed lookup is a value error. The displayed error is useful evidence, so read it before changing the formula:

#N/A usually means an exact match was not found. Check for extra spaces, different spelling, or numbers stored as text in one place and as numbers in another.
#REF! commonly indicates that the column index points beyond the selected range, or that a referenced cell or range has been deleted.
#NAME? can indicate that Excel does not recognize a function name or text in a formula was not enclosed in quotation marks.
#VALUE! indicates an invalid value or argument in the formula. For VLOOKUP, investigate the index and the length or source of the lookup value first.

Exact match does not mean that Excel ignores differences in the underlying data. For instance, a numeric value 123 and the text string “123” can behave differently in lookups. A value that looks identical on screen may also contain a leading or trailing space. Do not convert or trim a whole dataset blindly: first confirm the data’s intended meaning and whether leading zeros are significant, as they often are in product codes or postal codes.

A Practical Troubleshooting Sequence

Use a methodical check rather than replacing the formula with a broad error-handling formula straight away. With the formula cell selected, examine its arguments and compare them with the actual worksheet layout.

Confirm the lookup column. VLOOKUP searches the leftmost column of its selected range. If the key is to the right of the return column, VLOOKUP is not the right layout for that task.
Count the return column. Start at 1 on the left edge of the selected range and count to the intended return column. Confirm the index is a positive number and does not exceed the range width.
Check the lookup value. Confirm that the cell contains the expected value and is not an unusually long text string. If the value is calculated by another formula, inspect that formula too.
Check the range references. Make sure the table range includes both the lookup column and the desired return column. When copying a formula down, consider locking the table range with dollar signs.
Test exact match explicitly. For codes, names, and most ordinary key searches, include FALSE as the fourth argument so the intended match mode is clear.
Inspect the data types and characters. Look for text-versus-number differences, spaces, nonprinting characters, and inconsistent formats. Correct the source data where appropriate rather than hiding the mismatch.

For example, with lookup codes in A2:A100, descriptions in B2:B100, the search code in E2, and the formula copied down column F, use =VLOOKUP(E2,$A$2:$B$100,2,FALSE). The dollar signs keep the table range fixed when the formula is copied; the reference E2 remains relative and changes to E3, E4, and so on. This prevents the lookup table from sliding down the sheet and creating a different problem that can be mistaken for a bad formula.

Use Error Handling Only After Fixing the Cause

IFERROR can replace an error with a friendlier message, but it does not repair an invalid VLOOKUP. If it wraps a formula with a zero index, for example, the underlying problem remains hidden. A workbook may then show “Not found” even though the actual issue is a malformed formula rather than a missing key.

With keys in A2:A100, results in B2:B100, and the lookup value in E2, this formula displays a message for any error raised by the lookup: =IFERROR(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Check lookup"). The message is useful only if it is appropriate to treat all possible errors alike. Because IFERROR catches more than missing matches, use it carefully when diagnosing a workbook.

If the only error you want to handle is a missing match, IFNA is narrower. With the same layout, use =IFNA(VLOOKUP(E2,$A$2:$B$100,2,FALSE),"Not found"). This leaves errors such as #VALUE! visible for investigation while replacing #N/A with a readable message. Choose the handling method based on what the workbook should communicate, not simply on a desire to remove error displays.

When to Use a Different Lookup Method

VLOOKUP remains useful for a straightforward table where the search key is in the first column and the return value is to its right. Its fixed column index can become fragile, however: inserting or rearranging columns may make the formula return the wrong field or require its index to be updated. It also cannot naturally return a value from a column to the left of the lookup column.

XLOOKUP, available in Microsoft 365 and Excel 2021, takes separate lookup and return ranges and can search in either direction. For a key in D2:D100, a result in B2:B100, and a search value in E2, the formula =XLOOKUP(E2,D2:D100,B2:B100,"Not found") searches D and returns the corresponding value from B. This avoids counting a return-column index, but it is not suitable if the workbook must be opened in an Excel version that does not support XLOOKUP.

For older versions or workbooks where VLOOKUP is already required, fixing the argument and data issue may be the simplest solution. Changing functions is not automatically better; consider compatibility, how colleagues will maintain the workbook, and whether the lookup layout is likely to change.

Frequently Asked Questions

Why does VLOOKUP return #VALUE! instead of #N/A?
Those errors point to different issues. #N/A generally means an exact match could not be found, while #VALUE! points to an invalid argument or value. Check for a column index below 1 and a lookup string longer than 255 characters.

Can a zero column number cause a VLOOKUP value error?
Yes. VLOOKUP column indexes start at 1, so zero is invalid and can return #VALUE!. Use 1 for the first column in the selected range, 2 for the second, and so on.

Does a column number that is too high cause #VALUE!?
Usually not. If the index is greater than the number of columns in the table range, VLOOKUP normally returns #REF!. Expand the range or use the correct index for a column inside it.

Can spaces make VLOOKUP return #VALUE!?
Spaces more commonly cause an exact match to fail with #N/A, because the text values are different. Still, inspect copied or imported data for spaces and other hidden characters when troubleshooting a lookup.

Should I use IFERROR to hide the error?
Only if the replacement message is appropriate for every error it catches. To handle a missing match specifically, IFNA is usually more targeted; it allows other problems, including #VALUE!, to remain visible.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.