Excel VLOOKUP Not Working? Fix Formula Errors

Errors & fixes · Updated November 19, 2026

If VLOOKUP is not returning the result you expect, check the lookup range and use an exact match: with the value to find in E2, lookup keys in A2:A100, and results in B2:B100, enter =VLOOKUP(E2,A2:B100,2,FALSE). This searches column A for E2 and returns the matching value from column B; the checks below help identify why it may still fail.

How VLOOKUP works

VLOOKUP searches vertically down the first column of a selected range, then returns a value from another column on the same row. Its arguments are the value to look up, the table range, the number of the return column within that range, and an optional match setting. The return-column number starts at the left edge of the selected range, not at column A of the worksheet.

For example, if the table range is B2:D100, column 1 means B, column 2 means C, and column 3 means D. A common mistake is to count worksheet columns instead of columns inside the range. The lookup column also has to be the first column in the selected range, and VLOOKUP can return values only from columns to its right.

The last argument controls matching. FALSE requests an exact match; TRUE, or leaving the argument out, requests an approximate match. For most lists of names, codes, product IDs, or invoice numbers, exact matching is the safer choice. An approximate match is intended for ordered ranges such as tax bands or grading thresholds, and the first column should be sorted in ascending order for reliable results.

Start with the match setting and range

Inspect the formula in the formula bar and confirm that the fourth argument is FALSE. Without it, Excel uses approximate matching, which can return a nearby value rather than the exact row you intended. That result can look plausible, so it is particularly important to specify the match type in formulas used for IDs or records.

Assume A2:A4 contains P-101, P-104, and P-109, B2:B4 contains 8.50, 12.40, and 6.00, and E2 contains P-104. This formula returns 12.40:

=VLOOKUP(E2,A2:B4,2,FALSE)

Check that the selected range includes both the lookup column and the result column, and that the lookup column is at the range’s left edge. If you selected B2:B100 as the range but the keys are in A, VLOOKUP cannot search A. If the result is in a column to the left of the key, VLOOKUP is not the right tool unless you rearrange the data or use another lookup method.

Diagnose errors and unexpected results

The error itself is a useful clue. A #N/A result usually means an exact match was not found, though an incorrect lookup range or a data-type mismatch can cause the same symptom. #REF! often means the return-column number is larger than the number of columns in the selected range. A zero or a result from the wrong field can point to an incorrect column number. If Excel displays the formula characters in the cell instead of calculating, investigate text formatting or Show Formulas.

  • #N/A: Confirm the lookup value exists in the first column and check for hidden spaces or numbers stored as text.
  • #REF!: Count the columns in the selected range and make sure the return-column number does not exceed that count.
  • Wrong but plausible result: Add FALSE as the final argument and verify that the return-column number points to the intended field.
  • Formula displayed as text: Check whether the cell is formatted as Text or whether a leading apostrophe was entered.
  • Old result: Check Excel’s calculation mode and whether the source data has changed.

A genuinely blank cell in the return column can appear as 0 in the result, which is different from a failed lookup. Check the matched source row before changing the formula. If a zero is not meaningful in your report, decide whether to display a blank or explanatory label, rather than assuming the lookup failed.

Check numbers, text, and hidden spaces

Values that look identical on screen may not be identical to Excel. A code such as 104 can be stored as a number in one place and as text in another. Leading zeros are another warning: the text code 00104 is not the same value as numeric 104. Decide whether the field is a number or an identifier, then make the lookup value and table keys consistent. For identifiers, preserving text and leading zeros is often important.

Spaces can also prevent a match. A copied value may have an ordinary leading or trailing space, while a nonbreaking space from a web page can look exactly like a regular space. With a lookup value in E2 that has an ordinary extra space and a helper cell F2, this formula trims leading and trailing spaces and returns P-104:

=TRIM(E2)

TRIM does not remove every unusual character, including all nonbreaking spaces. If trimming does not resolve the mismatch, inspect the source or clean the imported data with an appropriate method. Avoid changing the displayed format alone: number formatting changes how a value looks, but it does not necessarily convert text to a number or make two different underlying values equal.

For a numeric value stored as text, you can convert a clean numeric text entry in a helper column. With the text 104 in A2, this formula returns the number 104:

=VALUE(A2)

Apply the same conversion consistently to the lookup value and the table keys where appropriate. Do not convert codes with meaningful leading zeros into numbers, because that can discard those zeros.

Make the lookup range stable

When you copy a VLOOKUP formula down a column, Excel may shift a relative table range. That can make later rows search a smaller or different part of the table. If E2:E20 contains the values to find, A2:A100 contains the keys, and B2:B100 contains the results, use dollar signs to keep the table fixed while allowing the lookup cell to change. For E2 equal to P-104, the formula returns 12.40:

=VLOOKUP(E2,$A$2:$B$100,2,FALSE)

The dollar signs lock the table reference when the formula is filled down; they do not lock E2. You can also select a reference while editing and press F4 on Windows to cycle through reference styles. On Mac, the shortcut depends on keyboard settings and may require Fn+F4; inserting dollar signs directly is an alternative.

For a changing data set, an Excel Table can be more convenient than fixed cell addresses because structured references and table ranges can expand as rows are added. If you keep an ordinary range, check that new records are included in the formula. A lookup cannot find a key that lies outside its selected table_array.

Confirm Excel is calculating the formula

If the formula is syntactically correct but the displayed answer appears stale, go to Formulas > Calculation Options and choose Automatic. In Manual calculation mode, Excel may not recalculate every dependent formula after an edit. Press F9 on Windows to recalculate open workbooks; on Mac, use Fn+F9 on keyboards where the function keys control system features.

If the cell shows the literal formula, first check whether the cell has Text formatting. Change the cell format to General, then re-enter the formula—for example, select the cell, press F2, and press Enter on Windows. Merely changing the format may not convert an existing text entry into a working formula. Also look for an apostrophe before the equals sign. If many formulas appear instead of results, toggle Show Formulas from the Formulas tab; Ctrl+` is the Windows shortcut, while the Mac shortcut can vary by keyboard layout.

In some regional settings, Excel uses semicolons rather than commas between formula arguments. The examples here use comma separators. If Excel rejects a correctly structured example, check the list separator expected by your installation rather than changing the lookup logic.

Show a helpful message for missing keys

When a missing value is an expected possibility, wrapping an exact-match lookup in IFNA can make a report easier to read. With E2 as the search value, A2:A100 as the key list, and B2:B100 as the result list, this returns the matching result or Not found if there is no match:

=IFNA(VLOOKUP(E2,A2:B100,2,FALSE),"Not found")

IFNA handles #N/A specifically, so it does not conceal other problems such as an invalid return-column number. That is usually preferable to replacing every error with a generic message: a #REF! may indicate a broken formula that needs repair, not simply a missing record. IFNA is available in current Excel versions, including Microsoft 365.

When another lookup method is a better fit

VLOOKUP is useful when the key is in the first column of the range and the desired result is to its right. If you need to return a value to the left of the key, or want a formula that identifies lookup and return ranges separately, XLOOKUP is often more flexible. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

For a Microsoft 365 or Excel 2021 workbook with the search key in D2:D100, product names in A2:A100, and the requested key in F2, this exact-match formula returns the product name for the matching key:

=XLOOKUP(F2,D2:D100,A2:A100,"Not found")

INDEX and MATCH are another option in versions that do not have XLOOKUP, and can look in one column and return from another even when the result column is to the left. If you maintain a workbook for other people, consider their Excel versions before switching functions; a newer formula may not calculate in an older installation.

Frequently Asked Questions

Why does VLOOKUP return #N/A even though the value is visible?
Check whether the lookup value and key are stored in the same format, and look for leading, trailing, or nonbreaking spaces. Also confirm that the visible value is in the first column of the selected range and that the formula uses FALSE for an exact match.

Why is VLOOKUP returning the wrong row?
The formula may be using approximate matching because its last argument is TRUE or omitted. Specify FALSE for exact matching, then verify that the selected range and return-column number correspond to the intended fields.

Why does VLOOKUP show 0 instead of a blank?
The matching row may contain a genuinely empty return cell, which VLOOKUP can display as zero. Inspect that source cell and decide whether the report should show zero, blank, or a separate label.

Can VLOOKUP search to the left?
No. It searches the first column of its selected range and returns a value from a column to the right. Use XLOOKUP in Microsoft 365 or Excel 2021 and later, or use INDEX and MATCH when compatibility with older versions matters.

Why does Excel show the formula instead of the answer?
The cell may be formatted as Text, the formula may have a leading apostrophe, or Show Formulas may be on. Set the cell to General and re-enter the formula, and check the Show Formulas command on the Formulas tab.

Related

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.