VLOOKUP #N/A Error: Causes and Fixes

VLOOKUP returns #N/A when it cannot find the lookup value in the first column of the search range. To fix it, set the last argument to FALSE for an exact match, confirm the value exists in that first column, and check for differences such as extra spaces or numbers stored as text.

What #N/A means in VLOOKUP

#N/A means “not available”: Excel has no result to return for the lookup value under the matching rules in your formula. It is often a useful signal that a key is missing from the source data, rather than evidence that Excel is broken. For example, a product ID in an order sheet may not yet exist in the product list.

The error can also appear when a value that looks present is not actually an exact match. A cell containing the number 125 and a cell containing text “125” may look identical on the worksheet but be different values. Similarly, a trailing space in one cell can make two displayed names fail to match.

VLOOKUP is not the only formula that can return #N/A, so check the formula bar and the surrounding formula if the error appears inside a larger calculation. In this guide, the focus is on errors produced by VLOOKUP itself and on fixing the reason for the missing match.

How VLOOKUP searches for a match

VLOOKUP searches down the leftmost column of a range you specify, finds a matching lookup value, and returns a value from another column on that same row. Its arguments identify the value to search for, the table range, the number of the return column, and whether to use an exact or approximate match. The return-column number is counted from the left edge of the selected range, not from the worksheet’s column A.

For instance, if the search range is A2:B6, column A is column 1 and column B is column 2. VLOOKUP cannot search column B and return a value from column A; the search column must be the range’s first column. This is a common limitation behind a lookup that seems logically correct but produces an error.

The final argument matters. Use FALSE (or 0) to request an exact match, which is usually right for names, IDs, invoice numbers, and product codes. If you omit the argument, Excel assumes approximate matching. Approximate mode is intended for ranges such as tax bands or grading thresholds, and normally requires the first column to be sorted in ascending order. An unsorted range can return an incorrect result rather than a clear error.

Run these checks first

Work through these checks before changing or wrapping the formula. They help separate a genuinely missing key from a formula setup problem:

Check the match setting. If you need an exact key match, make sure the last argument is FALSE. Do not rely on the default.
Look in the first column of the selected range. The lookup value must appear there, not merely somewhere else in the table.
Compare the underlying values. Check whether one value is text and the other is numeric, or whether either includes hidden spaces.
Confirm the range covers the data. A newly added row below the selected range cannot be found by a formula that still points only to earlier rows.
Check the return-column number. A number that points outside the selected range usually produces #REF!, not #N/A, but it is still worth verifying the formula’s arguments.
Inspect the source record. If the key is genuinely absent, add or correct the source data rather than trying to force a match.

Suppose product codes are in A2:A6, descriptions are in B2:B6, and the code to find is in E2. With an exact-match formula using the full table range, the result will be the description on the matching row. If E2 contains a code that is not in A2:A6, the result is #N/A; that is the expected result for a missing code.

Fix approximate-match and range problems

A frequent cause is an omitted final argument. When the formula does not specify exact matching, VLOOKUP uses approximate matching. For a list of unique IDs, this can fail to find a value that is present or return a result from a nearby row, especially if the first column is not sorted. Changing to exact matching is generally the safest correction for ordinary lookup tables.

With product codes in A2:A6, descriptions in B2:B6, and the code to find in E2, use this formula when the code must match exactly:

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

The dollar signs keep the table range fixed if you copy the formula down. Without them, a copied formula may shift its lookup range, leaving later rows outside the intended data. If the source list grows, extend the range or convert it to an Excel table so formulas can refer to a growing set of rows.

Approximate mode does have legitimate uses. For example, a commission table can assign a rate based on the greatest threshold that does not exceed a sale amount. In that case, the threshold column should be sorted smallest to largest, and the approximate-match behavior should be intentional. If a lookup value is below the smallest threshold, VLOOKUP can return #N/A because there is no lower threshold to use.

Resolve text, number, and spacing mismatches

Formatting a cell to display a number with leading zeroes does not necessarily change the underlying value. A numeric ID of 37 formatted to show as 0037 is still the number 37; a text ID containing “0037” is a different value. For identifiers where leading zeroes matter, keep the keys consistently stored as text. Converting such IDs to numbers can destroy meaningful zeroes.

Spaces and nonprinting characters can cause similar trouble, especially when data has been pasted from a website or exported from another system. Compare the source and lookup cells carefully, or use a helper column to clean the source keys. With the original key in A2, this helper formula removes ordinary extra spaces at the beginning and end and nonprinting characters recognized by CLEAN:

=TRIM(CLEAN(A2))

TRIM does not remove every unusual whitespace character, including the nonbreaking space that can occur in copied web data. If the values still fail to match, inspect or clean the source using the character that is actually present; replacing characters indiscriminately can alter valid IDs. A helper column is often safer than changing the original data, because it lets you compare the cleaned value with the original.

Exact matching in VLOOKUP is not case-sensitive, so “abc” and “ABC” are treated as equal. If capitalization is important to your data, VLOOKUP alone will not enforce that distinction. Also note that the wildcards * and ? can act as pattern characters in exact-match text lookups. A tilde, ~, is used to search for a literal wildcard character.

Check copied formulas and lookup direction

A correct formula in one row can become incorrect when copied. Check whether the table range moved, whether the lookup-cell reference points to the intended row, and whether the return-column index still describes the selected range. For example, a range written as A2:B6 may shift to A3:B7 when copied down; absolute references such as $A$2:$B$6 prevent that shift.

Also verify that the lookup value is on the left side of the table range. If the key is in column C and the result you want is in column A, VLOOKUP cannot return values to the left of its search column. Reorder or copy the data into a suitable lookup range, or use a function that can search and return from separate ranges.

To inspect a failing result, select the formula cell and review the formula bar. You can also use Formulas > Evaluate Formula in Excel for Microsoft 365 on Windows to step through parts of a formula. On Mac, the Evaluate Formula command is also on the Formulas tab, though its placement can vary with the ribbon layout. These checks help reveal a shifted range or an unexpected cell value without changing the source data.

Show a friendly result without hiding data issues

If a missing match is normal and you want a readable label instead of an error, wrap the lookup in IFNA. With product codes in A2:A6, descriptions in B2:B6, and the requested code in E2, use:

=IFNA(VLOOKUP(E2,$A$2:$B$6,2,FALSE),"Not found")

This returns the matching description when one exists and the text “Not found” when VLOOKUP returns #N/A. IFNA catches only #N/A, so other problems such as #REF! remain visible. That is often preferable to IFERROR, which can hide a wider range of formula problems and make a broken reference look like an ordinary missing record.

Use a friendly result only when it suits the workflow. In an audit or data-validation task, leaving #N/A visible may be more useful because it draws attention to records that need review. Replacing it with an empty string can make a missing match difficult to distinguish from a legitimately blank return value.

When XLOOKUP is a better alternative

For many lookups, XLOOKUP is easier to read because its lookup range and return range are separate, so the return column can be to the left or right of the search column. It also uses exact matching by default and allows you to specify what to display when no match is found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; it is not available in every older Excel version.

With lookup values in A2:A6, descriptions in B2:B6, and the requested code in E2, this formula searches for an exact match and displays a label if there is none:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found")

In Microsoft 365, XLOOKUP can reduce common VLOOKUP range and column-index mistakes, but it does not correct bad source data. A text-versus-number mismatch or a genuinely missing key still needs attention. If a workbook must work in older Excel versions, VLOOKUP may remain the more compatible choice.

Frequently Asked Questions

Why does VLOOKUP return #N/A when the value is visibly there?
It may be stored differently in the two places, such as text in one cell and a number in the other, or it may contain extra spaces or hidden characters. Confirm that the value is in the first column of the selected range and that the formula requests an exact match.

Can I make VLOOKUP return a blank instead of #N/A?
Yes. With the same lookup layout described above, you can use IFNA and provide an empty text value as the fallback. However, a blank can conceal a missing record, so use a clear label such as “Not found” when people need to investigate unmatched keys.

Does VLOOKUP distinguish uppercase and lowercase letters?
No. VLOOKUP’s text matching is not case-sensitive. If the case itself must be validated, use a case-sensitive approach rather than assuming an ordinary VLOOKUP will tell “abc” apart from “ABC.”

Why does VLOOKUP find one row but not another after I copy it?
The lookup range may have shifted as the formula was copied, or the later lookup value may contain a different data type or hidden characters. Lock the table range with dollar signs, then inspect the formula and the underlying values in the failing row.

Is #N/A always a formula error?
No. It can be the correct result when the key is absent from the source list. Decide whether that absence is expected: fix the data or key if it is not, and use an IFNA fallback only when a readable missing-match message is appropriate.

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.