Fix the VLOOKUP error

What VLOOKUP means

When someone says VLOOKUP 'is not working', the formula is either returning an error (#N/A, #VALUE!, #REF!), returning a wrong result, or the cell is displaying the formula text instead of a result. Each symptom has a different root cause and requires a different fix.

Common causes

• The formula is displaying as text because the cell is formatted as Text or there is a leading apostrophe — the formula is never evaluated.
• A #REF! error is appearing because col_index_num is larger than the number of columns in table_array.
• A #VALUE! error is appearing because col_index_num is less than 1, or is non-numeric (e.g. a text string was passed by mistake).
• The formula syntax is wrong — VLOOKUP requires exactly four arguments; a missing comma or wrong bracket causes a parse error.
• The lookup range spans multiple sheets or uses a named range that has since been deleted or renamed, breaking the reference silently.

Example fix

The table range D:F has only 3 columns, so col_index_num 5 caused #REF!. Corrected to 2 to return the second column.

How to fix it

1. 1If the cell shows formula text: check the cell format (Home > Format Cells in Excel, Format menu in Sheets) and switch it from Text to General, then re-enter the formula.
2. 2Fix #REF!: count the columns in your table_array. If table_array is D:F that is 3 columns — col_index_num cannot exceed 3.
3. 3Fix #VALUE!: make sure col_index_num is a positive whole number (1 or greater). Check that no surrounding formula is accidentally passing text into that argument.
4. 4Fix syntax errors: the correct signature is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Use the formula builder / function tooltip to verify each argument.
5. 5If the named range or sheet reference is broken, redefine the named range (Formulas > Name Manager in Excel) or correct the sheet tab name in the reference.

Frequently asked