IFERROR With VLOOKUP: Replace #N/A With a Message

IFERROR with VLOOKUP replaces the #N/A error you get when a lookup value is missing with something friendlier, such as "Not found", 0 or a blank cell. You wrap the whole VLOOKUP inside IFERROR and add the value to show instead. This guide shows the formula step by step, the three most useful replacement values, and when IFNA is the better choice.

Why does VLOOKUP return #N/A?#

VLOOKUP returns #N/A ("not available") when it can't find the lookup value in the first column of the table. Here is a price list in A1:C6:

With P-103 in E2, =VLOOKUP(E2,A2:C6,3,FALSE) returns 8.99. Change E2 to P-109, a code that isn't in the list, and the same formula shows #N/A. That is correct behaviour, but a column full of #N/A looks broken, and any SUM that includes it also returns #N/A. If VLOOKUP itself is new, start with VLOOKUP step by step.

How to write the IFERROR VLOOKUP formula#

IFERROR checks a formula and, if it returns any error, shows a value you choose instead:

=IFERROR(value, value_if_error)

To use it with VLOOKUP, put the whole lookup in the first argument:

=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found")

The easiest way to type it:

1. Write and test the VLOOKUP on its own first: =VLOOKUP(E2,A2:C6,3,FALSE).
2. Click in the formula bar just after the = and type IFERROR(.
3. Click at the very end and type ,"Not found").
4. Press Enter.

With P-103 in E2, the cell still shows 8.99. With P-109, it now shows Not found. If you plan to fill the formula down, lock the table first: $A$2:$C$6. The IFERROR function guide and the VLOOKUP function guide list every argument.

What to show instead of #N/A#

The second argument of IFERROR can be any value. Pick it based on what happens to the result next:

Be careful with 0. A price of 0 looks like a real price, so a reader can't tell "missing" from "costs nothing". A message is clearer unless the cell feeds a total. The empty-text version looks blank, but the cell still holds a formula, so COUNTA counts it.

Why IFERROR can hide real mistakes#

IFERROR catches every error, not only #N/A. That includes errors that mean your formula is wrong:

• =IFERROR(VLOOKUP(E2,A2:C6,4,FALSE),"Not found") asks for column 4 of a 3-column table. VLOOKUP returns #REF!, and IFERROR turns it into "Not found", even for codes that exist.
• A typo in a sheet or range name causes #NAME? or #REF!, which also gets hidden.

So "Not found" may really mean "this formula is broken". When a result looks wrong, delete the IFERROR part for a moment and see the real error. The guide to IFERROR in Excel explains which errors you should never hide.

IFNA: the safer choice for lookups#

IFNA works like IFERROR but only catches #N/A, the error for "value not found". Every other error still shows, so you notice when the formula itself is broken:

=IFNA(VLOOKUP(E2,A2:C6,3,FALSE),"Not found")

For a missing code, IFNA shows Not found, just like IFERROR. For the column-4 mistake above, IFNA shows #REF!, which tells you to fix the column number. IFNA is available in Excel 2013 and later. The comparison of IFERROR vs IFNA goes through more cases.

How to search a second table when the first has no match#

IFERROR's second argument can be another formula, so you can chain lookups. If a code isn't in the current price list in A2:C6, look in an older list in H2:J6:

=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),IFERROR(VLOOKUP(E2,H2:J6,3,FALSE),"Not found"))

Excel tries the first table. If that fails, it tries the second. If both fail, it shows Not found. Keep chains short: two or three lookups are easy to follow, while longer chains are hard to check. Combining the lists into one table is often the better long-term fix.

Other ways to handle missing values#

Newer lookup functions have this built in. In Excel 2021 and Microsoft 365, XLOOKUP's fourth argument is the value to show when nothing matches, and it only applies to missing values:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")

INDEX with MATCH can be wrapped in IFERROR or IFNA exactly like VLOOKUP. The article on VLOOKUP vs INDEX MATCH compares the two, and VLOOKUP vs HLOOKUP covers tables laid out across rows instead of down columns.

If IFERROR shows "Not found" for a code you can see in the table, the lookup is failing for a hidden reason. Check for extra spaces (TRIM fixes them), numbers stored as text, and a table range that doesn't start at the lookup column.

IFERROR VLOOKUP down a whole column#

In a real sheet you usually look up a whole column of codes, not one. Say the codes to find are in E2:E20. Lock the table with $ signs and fill the formula down from F2 (Windows: Ctrl+D; Mac: ⌘D):

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

E2 has no $, so it moves to E3, E4 and so on, while the table stays put. Then count the misses in one cell:

=COUNTIF(F2:F20,"Not found")

This tells you at a glance how many codes need attention. To see which ones, turn on a filter (Data > Filter) and show only "Not found" in column F. Fixing those codes, rather than just hiding the errors, keeps the data clean.

Practise it#

IFERROR around VLOOKUP is a classic job-test task because it combines two skills in one formula. Practise VLOOKUP in a live spreadsheet until the four arguments feel automatic, then practise IFERROR to wrap it cleanly. Each lesson checks your formula instantly. Or try the exercise on this page first.