Lookup Value Not Working in Excel? Fix the Cause

A lookup value usually fails in Excel because the lookup and source values do not match exactly, the formula uses the wrong match mode, or the lookup range is incorrect. Check the error first, then verify the value’s spelling and data type, the formula’s ranges, and whether it is set to find an exact match.

What a lookup value does

In a lookup formula, the lookup value is the item Excel searches for: perhaps a product code, employee name, date, or invoice number. The formula compares it with values in a search range and returns a related value from another range. If Excel cannot find a match under the formula’s rules, the result may be an error, an unexpected result, or a blank.

For example, suppose a price list has product codes in column A and prices in column B. A lookup searches for a code such as P-104 in column A, then returns its price from column B. Excel is not judging whether two entries look similar to a person; it applies the formula’s matching rules to the cell contents. A trailing space, a number stored as text, or a reference to the wrong column can therefore break a lookup that appears obvious at a glance.

Start by clicking the result cell and reading the formula in the formula bar. Identify which function is being used, which cell supplies the lookup value, which range is searched, and what match mode applies. That quick inspection often reveals the problem before you change the data.

Diagnose the result before changing anything

The displayed result provides a useful clue, but it does not always identify the cause on its own. Common symptoms include:

#N/A: Excel did not find a match under the formula’s rules. The values may differ, the search range may be wrong, or the match mode may be unsuitable.
The wrong answer: An approximate match may be active, the lookup range may be misaligned, or a column number may point to the wrong return column.
A blank result: The formula may be returning a blank from the matched row, or an IFERROR-style formula may be hiding an underlying error.
#VALUE! or another formula error: Check the arguments, references, and syntax; the issue may be separate from whether the lookup value exists.

Do not immediately wrap the formula in an error-handling function just to hide #N/A. That can make a report look cleaner, but it also conceals whether the lookup failed because the source data is incomplete or because the formula is incorrect. First test the underlying lookup and confirm what should happen when there is no match.

Check exact match and approximate match settings

Match mode is one of the most common causes of confusing results. XLOOKUP uses exact matching by default. VLOOKUP, however, uses approximate matching when its fourth argument is omitted or set to TRUE. Approximate matching is intended for situations such as tax bands or grading thresholds, and it generally requires the lookup column to be sorted in ascending order. For ordinary codes, names, and IDs, an exact match is usually the appropriate choice.

With product codes in A2:A5, prices in B2:B5, and the code P-104 in E2, this XLOOKUP formula returns 25 when the matching row has a price of 25:

=XLOOKUP(E2,A2:A5,B2:B5,"Not found",0)

The final 0 explicitly requests an exact match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If the code is absent, this example returns Not found instead of #N/A.

With the same layout—codes in A2:A5, prices in B2:B5, and P-104 in E2—this VLOOKUP formula also returns 25:

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

FALSE requests an exact match. If the fourth argument is left out, VLOOKUP may return a plausible but incorrect value because it assumes approximate matching. An exact-match lookup does not require the codes to be sorted.

Verify the lookup and return ranges

Look carefully at the range addresses in the formula. The lookup value must be compared against the column or row that actually contains the keys, and the return range must correspond to those same records. If a table has been moved or extended, an old formula might still point to an incomplete range and miss newer entries.

VLOOKUP has an additional limitation: its search column must be the first column of the table array, and its column index counts from the left edge of that array. For example, in A2:B5, column A is 1 and column B is 2. If the formula searches A2:B5 but uses 3 as the column index, the requested return column is outside the selected range. If the lookup key is to the right of the value you want to return, VLOOKUP cannot search left; use XLOOKUP or another method instead.

When copying formulas down a worksheet, inspect whether the ranges shifted. A reference such as A2:A5 normally adjusts when copied. If every row should search the same source list, use absolute references, such as $A$2:$A$5. In Excel, select a reference in the formula bar and press F4 on Windows to cycle through reference styles; on many Mac keyboards, use Command+T to cycle references, though keyboard settings can affect the shortcut. You can also type the dollar signs directly.

Look for text, number, and date mismatches

Two cells can display the same characters while storing different kinds of values. A number such as 104 may be numeric in one cell and text in another. Formatting a cell to look like a number does not necessarily convert text into a number. Similarly, dates can be stored as text rather than as Excel date serial values. An exact lookup can fail when the underlying types differ.

Check a few source and lookup cells rather than judging only by appearance. Select each cell and inspect the formula bar; notice whether numbers are left-aligned by default, whether a green error indicator appears, and whether the date behaves like a real date when formatted. Alignment can be changed manually, so it is only a clue, not proof.

If a whole column contains numbers stored as text, select the affected cells and use the warning icon’s Convert to Number option when Excel offers it. Another Windows option is Data > Text to Columns, then Finish, after selecting the relevant column. Review the selection first: conversion changes the data, and codes with leading zeros may need to remain text. For example, converting the text code 00104 to a number can remove the leading zeros and change the identifier.

For dates, make sure both sides represent actual dates and use compatible values. Changing the display format alone changes how a value looks, not necessarily what it contains. If a source system exports dates as text, convert the source data carefully before relying on a lookup.

Remove hidden spaces and inconsistent characters

Copied data from websites, PDFs, and other systems may contain leading or trailing spaces, nonbreaking spaces, or nonprinting characters. These can make two text entries fail an exact match even when they look identical. A normal space and a nonbreaking space are different characters, so pressing Backspace at the end of a cell may not remove every troublesome character.

First compare the original value with the lookup value by editing each cell or pasting its contents into a plain-text editor. If cleanup is needed, work on a copy of the source column so the original data remains available. Excel’s TRIM function removes ordinary extra spaces from text, while CLEAN removes certain nonprinting characters; neither should be assumed to fix every imported character, especially nonbreaking spaces.

Also check for differences in punctuation, hyphens, leading zeros, and spelling. Case is usually not the issue: standard Excel lookup comparisons are generally not case-sensitive. Thus, ABC and abc ordinarily match in XLOOKUP or VLOOKUP, but A-104 and A–104 may not, because the second hyphen-like character can be a different Unicode character.

Choose a lookup method that fits the table

For Microsoft 365 on Windows, XLOOKUP is a flexible option for most new lookup formulas. It separates the search range from the return range, can search in either direction, uses exact match by default, and lets you specify a value for not-found cases. Use VLOOKUP when maintaining an existing workbook or when compatibility with older Excel versions matters, but state FALSE for exact-match lookups unless approximate behavior is intentional.

INDEX and MATCH are another established combination, including in older Excel versions. They can return a value from a column to the left or right of the search column, unlike VLOOKUP. If a workbook is shared with people using older versions, check which functions those versions support before replacing formulas. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in Excel 2019 or Excel 2016.

On Excel for the web, many common lookup formulas work, but the available editing experience and interface can differ from desktop Excel. Excel for Mac also supports common lookup functions, though ribbon layout and keyboard shortcuts may differ. The formula examples above use commas as argument separators; some regional settings use semicolons, so use the separator Excel expects in that installation.

A practical troubleshooting sequence

Work through the checks in order, changing one thing at a time. This makes it easier to identify the real cause and avoids silently modifying a large source table.

Click the result cell and identify the function, lookup-value cell, search range, return range, and match argument.
Search the source column manually for the exact value. Confirm that it appears within the formula’s selected range, not just somewhere else on the sheet.
Set an exact match for identifiers and ordinary text lookups. For VLOOKUP, use FALSE; for XLOOKUP, specify 0 if you want to make the setting explicit.
Compare the stored types and characters on both sides. Check text-versus-number differences, dates, leading zeros, spaces, and punctuation.
Confirm that the return range lines up with the search range and that any VLOOKUP column index points to the intended column.
Test the formula on one known record with a value you can verify independently. Then fill it down and inspect a few results, especially at the top and bottom of the source list.

If the lookup still fails, copy the formula into a temporary cell and simplify it by checking one argument at a time. For example, temporarily select the referenced search range and use Find to locate the key. Do not delete or overwrite source data until you know whether it is malformed or simply being referenced incorrectly.

Frequently Asked Questions

Why does Excel show #N/A when the lookup value is visibly there?
Check whether the formula searches the column containing that value and whether the relevant row is inside the selected range. Then compare the underlying data types and characters. A number stored as text, a hidden space, or a different hyphen can prevent an exact match even if the cells look alike.

Does VLOOKUP need the lookup column sorted?
Not when you use FALSE for an exact match. Sorting is generally required for VLOOKUP’s approximate-match behavior, which is used for ranges or thresholds rather than ordinary unique IDs. For a typical code lookup, use FALSE explicitly.

Why does VLOOKUP return the wrong row?
A likely cause is approximate matching because the fourth argument is omitted or TRUE. Another possibility is that the selected return-column number points to a different field than intended. Check the table array and use FALSE for an exact lookup.

Can Excel lookup formulas distinguish uppercase and lowercase?
Standard XLOOKUP and VLOOKUP matching is not case-sensitive, so ABC and abc ordinarily match. If case must matter, use a case-sensitive approach built around the EXACT function rather than assuming a normal lookup will distinguish them.

Why does the formula work for one row but not after I copy it?
The source range may have shifted as the formula was filled down. If every formula should search the same list, make the range absolute with dollar signs, such as $A$2:$A$100. Also check that the lookup-value reference changes to the intended row.

Will changing a cell’s format fix a lookup mismatch?
Not necessarily. Formatting changes the display, while the stored value may still be text, a number, or a date serial. Convert the underlying data to a consistent type when needed, and preserve identifiers such as codes with leading zeros as text.

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.