Excel Lookup Formula Not Working? Find and Fix the Cause

Errors & fixes · Updated November 19, 2026

If an Excel lookup formula is not working, first check that the lookup value and lookup range contain matching data, then confirm that the formula uses the right columns and match mode. In Microsoft 365, XLOOKUP is usually the clearest option because it defaults to an exact match; older formulas such as VLOOKUP can return a wrong result if their match argument is omitted.

Start by identifying the symptom

The error or result you see is a useful clue. A formula that returns an error usually points to a different problem than one that returns a plausible but incorrect value. Before changing the formula, click the result cell and inspect the formula bar to make sure you are troubleshooting the formula you expect.

  • #N/A: Excel did not find a match under the formula’s matching rules. The value may truly be absent, or it may differ because of spaces, data type, or characters that look alike.
  • #REF!: A reference is invalid. With VLOOKUP, for example, the column number may ask for a result column outside the selected table range.
  • #VALUE!: An argument may have an incompatible shape or value. XLOOKUP’s lookup and return arrays, for instance, need compatible dimensions.
  • #NAME?: Excel may not recognize a function name, or text in a formula may lack quotation marks. XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
  • A wrong value, not an error: Check for approximate matching, duplicate keys, or a lookup range that starts in the wrong place.

If a formula appears as text in the cell rather than calculating, check whether the cell is formatted as Text or whether the formula begins with an apostrophe. Change the number format to General, then re-enter the formula; changing the format alone may not recalculate text already entered as a formula.

Check the lookup formula’s structure

For a worksheet with product codes in A2:A5, prices in C2:C5, and the code to search for in E2, XLOOKUP searches the code column and returns the value from the price column. If E2 contains SKU-103 and C4 contains 12.50, this formula returns 12.50:

With product codes in A2:A5, prices in C2:C5, and the search code in E2, use: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")

XLOOKUP’s first argument is the value to find, its second is the search range, and its third is the return range. The optional fourth argument supplies a clearer message if there is no match. By default, XLOOKUP looks for an exact match, and it can return results from columns on either side of the lookup column. It requires Microsoft 365 or Excel 2021 or later.

The equivalent VLOOKUP for that same layout is:

With product codes in the first column of A2:C5, prices in the third column of that range, and the search code in E2, use: =VLOOKUP(E2,A2:C5,3,FALSE)

For E2 containing SKU-103 and a price of 12.50 in C4, the formula returns 12.50. The 3 tells VLOOKUP to return the third column of A2:C5; FALSE requests an exact match. VLOOKUP can only search the leftmost column of its table range and return a value to its right. Include FALSE for ordinary exact-match searches: omitting the last argument makes VLOOKUP use approximate matching, which can produce an unexpected result.

Make sure the values really match

Two cells can look identical while containing different values. A frequent cause is a number stored as text in one place and as a number in another. Cell formatting does not reliably reveal this difference: a number format changes how a value is displayed, but does not convert text into a number. Imported IDs, postal codes, and product codes often arrive as text, sometimes deliberately to preserve leading zeros.

Spaces and invisible characters are another common cause. A trailing space in “SKU-103 ” is not the same as “SKU-103”. Data copied from websites or other systems may contain nonbreaking spaces or other characters that ordinary trimming does not remove. A date can also appear to match while the underlying date-time values differ; one cell may include a time even when its display format hides it.

For a quick count of exact matches, use this formula:

With the values to search in A2:A100 and the lookup value in E2, use: =COUNTIF(A2:A100,E2)

If E2 is SKU-103 and exactly one cell in A2:A100 contains that value, the result is 1; if no cell matches, it is 0. A count greater than 1 signals duplicate keys, so a lookup may return only the first matching record. COUNTIF follows Excel’s normal criteria rules, including wildcard behavior for some text criteria, so it is a diagnostic rather than a definitive test of every possible character difference.

For a helper column that cleans ordinary extra spaces and many nonprinting ASCII characters, use:

With the original text in A2 and the cleaned result in B2, enter: =TRIM(CLEAN(A2))

If A2 contains a code with leading or trailing ordinary spaces, the result removes those spaces and reduces repeated internal spaces to one. CLEAN removes certain nonprinting characters, but neither function handles every Unicode character, including all nonbreaking spaces. Compare the cleaned values on both sides of a lookup, and preserve the original data if formatting or identifiers matter.

Verify ranges, direction, and match mode

Look at the exact range boundaries. If the lookup column is A but a VLOOKUP table range begins at B, the formula will search the wrong field. If a VLOOKUP return column is the fourth column but the range contains only three columns, its column index is invalid and the formula can return #REF!. For XLOOKUP, the lookup and return ranges should correspond row for row; accidentally selecting a return range of a different size can cause an error.

Approximate matching deserves special attention. VLOOKUP with TRUE, or with its final argument left out, assumes the first column is sorted in ascending order. Approximate matching is useful for bracket tables, such as commission bands, but unsorted data can give misleading results. For an exact lookup, use FALSE with VLOOKUP or rely on XLOOKUP’s exact-match default. XLOOKUP can also be explicitly set to other match modes, so check all its arguments if it was copied from another workbook.

Duplicates are not necessarily errors, but they affect which record is returned. XLOOKUP and VLOOKUP normally return the first match they encounter in the search direction. If two customers share an ID, or a product list contains repeated codes, decide whether the key should be unique or whether the task needs a summary, filter, or different key. A lookup formula does not automatically know which duplicate is the intended one.

Check references and copied formulas

When a formula works in one row but fails after being filled down, inspect how its references move. Relative references change as a formula is copied. If a lookup range should stay fixed while the lookup value changes from row to row, make the range absolute with dollar signs, or use a table with structured references. In the example below, the lookup value can move down a row while the data ranges remain fixed.

With codes in A2:A100, prices in C2:C100, and each row’s search value in E2, use: =XLOOKUP(E2,$A$2:$A$100,$C$2:$C$100,"Not found")

For E2 equal to SKU-103 and C4 equal to 12.50, this returns 12.50; when copied down, the fixed search ranges stay put. In the formula bar, also check for a deleted column, a misspelled sheet name, or a reference that points to an old workbook. If the lookup data is on another sheet, verify that the sheet name and range still exist.

For a complex formula, select its cell and use Formulas > Evaluate Formula to step through how Excel calculates it. Formulas > Trace Precedents can help show which cells feed into the result. These tools are particularly useful when the formula contains nested functions or references across sheets.

Check calculation and workbook state

Sometimes the formula is sound but the displayed result is stale. In Excel for Microsoft 365 on Windows, check Formulas > Calculation Options > Automatic. If the workbook is set to Manual, edits may not recalculate until you request a calculation. Press F9 to recalculate open workbooks, or use the calculation controls on the Formulas tab. Excel for Mac has calculation options as well; menu and ribbon presentation can differ by release.

Also confirm that the result cell is not displaying a previous value because calculation is paused, or showing the formula as literal text. A leading apostrophe or Text number format can prevent evaluation. After correcting the cell format, re-enter the formula. If only one workbook behaves strangely, compare its calculation setting with a new workbook before rewriting every lookup.

Choose an error message without hiding problems

An optional not-found message can make a report easier to read, but broad error handling can conceal a broken range or invalid formula. XLOOKUP’s fourth argument handles the no-match case directly. With E2 containing a code absent from A2:A5, this returns “Not found”:

With codes in A2:A5, prices in C2:C5, and an unmatched search code in E2, use: =XLOOKUP(E2,A2:A5,C2:C5,"Not found")

If the search code is SKU-999 and it is absent from the range, the result is Not found. This is more informative than an unexplained #N/A, while still allowing unrelated errors to appear. IFNA can be useful around a lookup when the intent is specifically to handle #N/A; IFERROR is broader and can hide problems such as invalid references. During troubleshooting, leave errors visible until you understand their cause.

When to use another lookup method

For most new exact-match lookups in Microsoft 365, XLOOKUP is a practical default: it does not need a numeric return-column index, can search in either direction, and lets you specify a no-match result. VLOOKUP remains useful for compatibility with older Excel versions and familiar workbooks, but its leftmost-column requirement and approximate-match default make careful arguments important.

INDEX and MATCH can provide flexible lookups in older Excel versions, including arrangements where VLOOKUP cannot return a value to the left of the search column. If a result should combine or summarize multiple matching rows rather than return the first one, a lookup may not be the right tool. Consider a PivotTable or, in supported versions, a filtering formula. FILTER is available in Microsoft 365 and Excel 2021 or later. These alternatives solve different tasks; they are not necessary fixes for a simple mistyped range.

Frequently Asked Questions

Why does VLOOKUP return the wrong value instead of #N/A?
Its final argument may be omitted or set to TRUE, which requests approximate matching. Use FALSE for an exact match, and make sure the lookup column is the first column in the selected table range.

Why does XLOOKUP return #N/A when the value is visible?
The visible text may contain extra or nonprinting characters, or one value may be text while the other is numeric. Check both underlying values, test for duplicate or mismatched data, and clean or convert data only when that conversion is appropriate.

Can XLOOKUP search to the left?
Yes. Set the lookup array to the column containing the key and the return array to the column containing the answer; the return column does not have to be to the right. This works in Microsoft 365 and Excel 2021 or later.

Why does my lookup work until I copy it down?
The lookup ranges may be moving along with the formula. Use absolute references for fixed ranges, such as $A$2:$A$100, while leaving the row-specific lookup cell relative if it should change.

Does changing a cell’s format convert text to a number?
No. Number formatting changes display, not the underlying value type. Convert the data with a suitable method, and be cautious with identifiers that need leading zeros or are meant to remain text.

Why is Excel showing the formula instead of its answer?
The cell may be formatted as Text, the entry may begin with an apostrophe, or Show Formulas may be enabled. Check the cell format and formula entry, and toggle Show Formulas from the Formulas tab if formulas are being displayed across the worksheet.

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.