Excel: Remove #N/A Errors From VLOOKUP Results Safely

To replace a VLOOKUP #N/A error with a readable message, wrap the lookup in IFNA: =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). This returns “Not found” when the lookup produces #N/A, while leaving other errors visible so you can still spot problems in the formula or data.

The Quick Fix: Wrap VLOOKUP in IFNA

In this example, the lookup value is in E2, the lookup table is A2:C100, and the value to return is in the third column of that range. Enter the formula in the result cell, such as F2, and fill it down for other rows. The dollar signs keep the table range fixed as the formula is copied; E2 changes to E3, E4, and so on.

The last VLOOKUP argument, FALSE, requests an exact match. That is usually what you want when matching product codes, employee IDs, names, or other identifiers. IFNA changes only the #N/A result into “Not found.” If VLOOKUP returns a valid value, that value appears as usual.

You can use a different message, such as “No match” or “Check code.” The replacement is text, so downstream calculations that expect a number may not work with it. If the result needs to remain numeric, consider using 0 as the replacement only when zero has a clear meaning in your workbook; otherwise, a message is less likely to be mistaken for a real result.

What #N/A Means in a VLOOKUP

#N/A means that Excel could not find a matching lookup value under the conditions in the formula. With an exact-match VLOOKUP, the value being searched for must match a value in the leftmost column of the table range. A nearly identical value is not necessarily a match: extra spaces, different characters, or a number stored as text can cause the lookup to fail.

There is an important distinction between fixing the cause and hiding the displayed error. IFNA makes a result easier to read, but it does not create a match or repair the source data. If a code should exist in the lookup table, investigate why it is missing before treating “Not found” as an acceptable final result.

Also check what “missing” means for the workbook. It could be a genuinely new item, a mistyped key, a value absent from the current table, or a mismatch between how two systems store the same identifier. A useful error message can help readers, but a good lookup process also makes it possible to identify and correct unexpected misses.

Choose an Exact-Match Formula

VLOOKUP can perform approximate or exact matching. When the fourth argument is omitted, VLOOKUP uses approximate matching, which expects the first column of the lookup range to be sorted in ascending order. For unsorted identifiers, omitting that argument can produce an incorrect result instead of the #N/A you expected. Include FALSE to require an exact match.

For the example above, the formula has four parts: E2 is the value to search for; A2:C100 is the table; 3 tells Excel to return a value from the third column of that range; and FALSE requests an exact match. The column number is relative to the selected range, not the worksheet. If the range starts in column B, then its first column is B, its second is C, and its third is D.

VLOOKUP searches only the leftmost column of the selected range and returns values from columns to its right. If the lookup key is not in that first column, changing the column index will not solve the problem. You may need to select a different range, reorganize the data, or choose a lookup function that can return a value from a column on either side.

Keep Blank Lookup Rows Blank

When a formula is copied down a long list, rows without a lookup value can show “Not found.” To leave those rows empty instead, test the lookup cell first. With lookup values in E2 and below, the table in A2:C100, and the desired return value in its third column, use:

=IF(E2="","",IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"))

This formula displays a blank when E2 is blank; otherwise, it performs an exact-match lookup and displays “Not found” if there is no match. It is useful for reports where unused rows should look clean. Be aware that the test treats a formula returning an empty string as blank too. If spaces or other placeholder characters are entered in the lookup cell, Excel may not consider that cell empty, and the lookup will still run.

Blank output is convenient, but it can make missing data less visible. In an operational list, “Not found” may be more helpful because it distinguishes an unfilled row from a lookup that was attempted and failed. Choose the display based on how people use the sheet, not just on appearance.

IFNA or IFERROR?

IFNA is usually the more precise choice when the goal is to handle a missing VLOOKUP match. It responds to #N/A and leaves other error types visible. IFERROR responds to any error, including errors such as #REF!, #VALUE!, and #DIV/0!, so it can conceal a broken reference or another issue that deserves attention.

For the same lookup value E2 and table A2:C100, with the result in the third column and exact matching enabled, this alternative replaces any error with a message:

=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")

Use that broader handling only if you intentionally want all errors to be replaced. For example, a formula with an invalid column index may produce an error that IFERROR hides as “Not found,” even though the actual problem is the formula setup. IFNA keeps that kind of non-#N/A problem visible. Both functions are available in Microsoft 365 for Windows.

Why VLOOKUP Can Still Return #N/A

If a key should be present but the formula says “Not found,” inspect the data and formula before settling for the message. Common causes include:

Extra spaces or hidden characters: A value such as “AB12” may not match “AB12 ” with a trailing space. Compare the source values and remove unwanted spaces or nonprinting characters where appropriate. TRIM can help with ordinary extra spaces, but it does not remove every kind of hidden character.
Numbers and text that look alike: The number 123 and text “123” can be stored differently. Check the cell formats and underlying values, then standardize the key column and lookup value consistently.
The lookup key is not in the first range column: VLOOKUP searches the left edge of its table array, even if the desired key appears elsewhere on the worksheet.
The selected range misses a row: Confirm that the lookup table includes the row containing the key. A fixed range such as A2:C100 will not include new records below row 100.
A changing or incorrect column index: The return-column number must refer to a column inside the selected range. Check that it points to the field you intended to return.
The key is genuinely absent: The source list may be out of date, or the item may not yet have been added. Confirm against the correct source before changing the lookup formula.

Duplicate keys are a different issue: VLOOKUP generally returns the first matching row it encounters, rather than reporting that the key appears more than once. A successful result therefore does not prove that the lookup key is unique. If duplicates would make the result ambiguous, check the source table as part of the cleanup.

How to Troubleshoot Before Hiding the Error

A practical check is to temporarily remove IFNA and examine the original VLOOKUP result. Confirm the exact lookup value, then search for it in the first column of the selected range. Compare the values rather than relying only on how they look on screen. If the lookup table changes often, verify that the formula range includes newly added records and that the return-column index still identifies the intended field.

For a quick manual search, select the lookup column and use Find. On Windows, press Ctrl+F; on Mac, press Command+F. Search for the value from the lookup cell, but remember that a visual search may not explain differences in data type or invisible characters. Also review the formula bar to make sure the lookup cell reference and range are what you intended.

After correcting a key or range, test more than one case: a known match, a known non-match, and a blank input if the formula will be copied into unused rows. This helps confirm that the formula returns the expected values and that its fallback message appears only for the cases you intend.

When XLOOKUP May Be a Better Alternative

In Microsoft 365, XLOOKUP is an option when you want a lookup function that can search one range and return a value from a separate range. Unlike VLOOKUP, its lookup and return ranges do not have to be arranged as a single table with the key at the left. XLOOKUP also uses exact matching by default. It is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual versions such as Excel 2019.

With the lookup value in E2, keys in A2:A100, and results in C2:C100, this XLOOKUP formula returns a custom message when no match is found:

=XLOOKUP(E2,A2:A100,C2:C100,"Not found")

The fourth argument is the value to display if no match exists, so a separate IFNA wrapper is not needed for that particular case. XLOOKUP does not remove the need to check data quality: inconsistent keys can still fail to match, and an error stored in a return cell is not the same thing as a missing lookup value. If a workbook must work in an older Excel release, VLOOKUP with IFNA is generally more compatible.

Frequently Asked Questions

How do I replace #N/A with a blank in VLOOKUP?
With the lookup value in E2 and the table in A2:C100, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). This returns an empty string for #N/A, though it can make a missing match look like a genuinely blank result.

Does IFNA fix the VLOOKUP problem?
No. IFNA changes how an #N/A result is displayed; it does not repair a missing key, an incorrect range, or inconsistent data. Investigate the cause if the lookup should have found a match.

Why does VLOOKUP show #N/A even though the value appears in the table?
The values may differ in a way that is not obvious, such as extra spaces, hidden characters, or a number stored as text. Also confirm that the key is in the first column of the selected range and that the formula uses the intended lookup value.

Should I use IFERROR instead of IFNA?
Use IFNA when you specifically want to handle a missing match. IFERROR hides all errors, including errors that may point to a broken formula, so it is broader than necessary for most VLOOKUP not-found cases.

Can IFNA hide an error in the return column?
Yes. If VLOOKUP finds a key but the corresponding return cell contains #N/A, the outer IFNA can replace that error too. If that distinction matters, inspect the source return cell or use a separate check to identify the source of the error.

Why does VLOOKUP return the wrong value instead of #N/A?
Check whether the formula omits the fourth argument or uses TRUE, which allows approximate matching and depends on sorted lookup data. For identifiers and unsorted lists, use FALSE for exact matching.

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.