How to correct a #VALUE! error in the VLOOKUP function

If you work with VLOOKUP, there is a good chance you may have run into the #VALUE! error several times. This topic lists the most common problems that may occur with VLOOKUP, and the possible solutions.

Problem: The lookup_value argument is more than 255 characters.

Solution: Shorten the value, or use a combination of INDEX and MATCH functions as a workaround.

Note: This is an array formula and needs to be entered with CTRL+SHIFT+ENTER. Excel will automatically wrap the formula in braces {}. If you try to enter them yourself Excel will display the formula as text.

Problem: The col_index_num argument contains text or is less than 0.

The Column Index is the number of columns to the right of the search column that you want to return if a match is found.

This may be due to a typo in the col_index_num argument, or accidentally specifying a number less than 1 as the index value (a common occurrence if another Excel function nested in the VLOOKUP function returns a number such as "0" as the col_index_num argument).

The minimum value for the col_index_num argument is 1, where 1 is the search column, 2 is the first column to the right of the search column and so on. So if you're searching in column A, then 1 references that, 2 is column B, 3 is column C, and so on.

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice.

See Also

Correct a #VALUE! error

Everything you need to know about VLOOKUP

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)