Excel VLOOKUP Error Messages: How to Create One

Errors & fixes · Updated November 19, 2026

To show a helpful message when VLOOKUP cannot find a match, wrap it in IFNA: =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Product not found"). Use IFERROR instead if you want to replace any formula error, not just a missing-match error.

In Microsoft Excel for Microsoft 365 on Windows, these functions let you control what appears in the result cell. The key is to handle expected lookup misses without concealing problems in the formula or source data. The examples below use comma separators; depending on your regional settings, Excel may require semicolons instead.

What a VLOOKUP error message means

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column in the same row. If an exact-match search cannot find the value, Excel normally displays #N/A. That is useful while building a workbook, but a reader may find a plain error code unclear. An error-handling function can replace it with a message such as “Product not found.”

The message is text in the result cell, not a change to how VLOOKUP searches. It does not add the missing item to the lookup table or correct a misspelled key. If the lookup succeeds, the formula returns the requested value as usual; if the specified error occurs, it returns the alternative text.

For an exact search, include FALSE as VLOOKUP’s fourth argument. If you omit that argument, VLOOKUP uses approximate matching by default. Approximate matching can return an unexpected row when the first column is not sorted appropriately, so exact matching is generally the safer choice for identifiers such as product codes, employee IDs, and invoice numbers.

Create a basic not-found message

Suppose product IDs are in A2:A100, product names are in column B, prices are in column C, and the ID to search for is in E2. Enter the formula in the cell where you want the price or message to appear:

With product IDs in A2:A100, names in B2:B100, prices in C2:C100, and the search ID in E2, enter this in the result cell:

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

For example, if E2 contains an ID that is absent from A2:A100, the result is Product not found. If E2 contains an ID found on a row where the price in column C is 12.5, the result is the number 12.5. You can format that result cell as currency if you want it displayed as a price.

  1. Select the result cell and type the formula, beginning with an equals sign.
  2. Check that the lookup range starts with the column containing the IDs. In this example, that is column A.
  3. Set the column number to 3 because the return value is in the third column of A:C.
  4. Keep FALSE for an exact match, then press Enter.
  5. If you copy the formula down, the dollar signs keep the lookup range fixed while E2 changes to E3, E4, and so on.

On Windows, you can use Ctrl+C and Ctrl+V to copy and paste a formula, or drag the fill handle at the lower-right corner of the selected cell. On Mac, use Command+C and Command+V. The formula itself works the same way in Excel for Mac and Excel for the web, although ribbon placement and some keyboard shortcuts can differ.

Choose IFNA or IFERROR

Use IFNA when the message should appear only for the “value not available” error, most often the #N/A produced when an exact lookup misses. Other errors remain visible. That is often preferable during workbook maintenance because an invalid column number or another formula problem is not silently passed off as a normal missing item.

Use IFERROR when you intentionally want the same fallback for any error value. It can catch errors such as #N/A, #REF!, and #VALUE!. The trade-off is that it may hide a mistake. For instance, if someone changes a formula’s column number so it points outside the lookup range, IFERROR could display “Lookup failed” rather than revealing the underlying #REF!.

  • Choose IFNA for a clear response to a missing exact match while leaving other errors visible.
  • Choose IFERROR when every error should produce the same fallback, and you have considered the risk of masking formula problems.
  • During setup, consider leaving errors visible until you have confirmed the lookup range and column number are correct.

Both functions are available in current Excel for Microsoft 365. IFERROR is also present in older Excel releases, while IFNA is available in Excel 2013 and later. For a current Microsoft 365 workbook, version availability is usually not an obstacle.

Make the message more useful

A fixed message is simple, but a message that includes the searched value can help someone identify what needs checking. With product IDs in A2:A100, prices in C2:C100, and the requested ID in E2, enter this formula in the result cell:

=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"No match for "&E2)

If E2 contains P-999 and that ID is absent from the table, the result is No match for P-999. The ampersand joins the fixed text and the cell value. If the key might be a date or a number and you need a specific display format in the message, convert it with TEXT and an appropriate format code; otherwise, Excel may display the value differently from how it appears in the input cell.

You can also distinguish an empty input from an unsuccessful search. With IDs in A2:A100, prices in C2:C100, and the lookup key in E2, use:

=IF(E2="","Enter a product ID",IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Product not found"))

If E2 is empty, this returns Enter a product ID. If E2 contains a value not in the table, it returns Product not found; if it matches, it returns the price. The blank check is useful in a prepared worksheet where users may not have filled in every input yet. Keep in mind that a cell containing a formula that returns an empty string is also treated as blank by this test.

Diagnose common VLOOKUP errors

An error message is most useful when it reflects the real condition. Before replacing an error, check the lookup value, the table range, and the column number. These are frequent causes of unexpected results:

  • #N/A: With an exact match, Excel did not find the lookup value in the first column of the selected range. Check spelling, extra spaces, and whether the value is stored as text in one place and as a number in another.
  • #REF!: The column number asks for a column beyond the right edge of the selected range, or a referenced cell or range has been deleted.
  • #VALUE!: The column number is less than 1, or another argument has an invalid value.
  • A plausible but wrong result: Check whether the fourth argument is missing or set to TRUE. Approximate matching can return a nearby value rather than an exact one.

Text and number mismatches are easy to overlook. A cell that displays 104 may contain the number 104, while another cell contains the text “104.” Those values may not match in the way you expect. You can inspect a cell’s contents and number format, and test a small sample before filling a formula down a long column.

Extra spaces can cause similar trouble, especially in data imported from another system. If the lookup value has a trailing space, it is not the same text as the version without that space. Cleaning the source data is usually more reliable than adding increasingly complicated error handling. IFNA can report a failed search, but it cannot tell you whether the reason was a genuinely absent record, a typo, or inconsistent formatting.

Keep error handling from hiding important information

Replacing an Excel error with text changes the output’s type. A successful VLOOKUP may return a number, while the fallback is a text string. That is fine for a display-oriented report, but a later calculation that expects a number may not be able to use the message as a numeric value. If other formulas depend on the result, decide whether they should handle text, use a separate status column, or receive a numeric value by another method.

A common misconception is that IFERROR or IFNA “fixes” VLOOKUP. It only changes the result shown when a particular error occurs. It does not make a bad column index correct, convert text IDs into numbers, or create a missing row in the lookup table. During development, inspect the raw error first; once the lookup is sound, add a message for cases that are expected in normal use.

For a workbook used by others, choose wording that tells them what to do without implying the data is definitely wrong. “No match for P-999” is more informative than “Error.” If the table may simply be incomplete, “Not found in the current list” is more precise than a message that suggests the item does not exist at all.

When VLOOKUP may not be the best fit

VLOOKUP can return values only from columns to the right of its lookup column. Its numeric column index can also become inaccurate if someone inserts or removes columns inside the selected range. If you are building a new workbook in Microsoft 365, XLOOKUP is often a more flexible option: it can search in either direction and has a dedicated argument for what to return when no match is found.

With product IDs in A2:A100, prices in C2:C100, and the lookup ID in E2, this XLOOKUP formula returns a message if no ID matches:

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

If E2 is absent from A2:A100, the result is Product not found; if it matches a row, XLOOKUP returns the corresponding value from column C. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel versions. If a workbook must work for users on older releases, VLOOKUP with IFNA is a compatible approach for those versions that include IFNA, or use IFERROR where appropriate.

Frequently Asked Questions

How do I show “Not Found” instead of #N/A in VLOOKUP?
Wrap the exact-match VLOOKUP in IFNA and put the message in quotation marks as its second argument. For example, with the lookup key in E2 and the table in A2:C100, use =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not Found"). The message appears when the lookup returns #N/A.

Should I use IFERROR or IFNA with VLOOKUP?
Use IFNA when you only want to handle a missing-match error and want other problems to remain visible. Use IFERROR if you deliberately want one fallback for any error value. IFERROR is broader, so it can conceal an incorrect range or column number.

Why does VLOOKUP show the message even though the value seems to be in the table?
Check that the lookup value and the first column of the table contain the same underlying value, not just values that look alike. A number stored as text, an extra space, a typo, or a lookup range that starts in the wrong column can prevent a match. Also confirm that the correct row is inside the selected range.

Can I make VLOOKUP show a message when the input cell is blank?
Yes. Put an IF check around the lookup so the blank-input case is handled first, then use IFNA for an unsuccessful search. This lets you show different messages for “Enter a product ID” and “Product not found,” rather than treating both conditions alike.

Does VLOOKUP need FALSE for an exact match?
For a dependable exact lookup, include FALSE as the fourth argument. If you omit the argument, VLOOKUP defaults to approximate matching, which can return an unintended result when the lookup column is unsorted or contains values close to the search key.

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.