VLOOKUP with IFERROR in Excel and Google Sheets
Purpose
VLOOKUP with IFERROR is one of the first formula combinations I teach anyone who uses lookups in a spreadsheet. VLOOKUP…
VLOOKUP with IFERROR is one of the first formula combinations I teach anyone who uses lookups in a spreadsheet. VLOOKUP is powerful but brutally honest — when it cannot find a value it returns a bright red #N/A error that breaks every formula downstream and makes reports look broken to anyone reading them. IFERROR wraps around VLOOKUP and catches that error before it reaches the cell, replacing it with whatever you choose: a friendly Not found label, a zero so your SUM still works, or an empty string so the cell just looks blank. Every VLOOKUP I write in production work is wrapped in IFERROR. It is that automatic. If you are on a newer version of Excel or Google Sheets, XLOOKUP handles the not-found case through its own fourth argument without needing a wrapper — but for any file that needs to work in Excel 2016 or earlier, IFERROR around VLOOKUP is the standard and correct approach.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=IFERROR(VLOOKUP(lookup_value, table_array, col_index, FALSE), value_if_error)
Arguments
| Argument | Required | Description |
|---|---|---|
| VLOOKUP arguments: lookup_value | Required | The value to search for — usually a cell reference like A2. Data type must match the lookup column. |
| table_array | Required | The table range containing both the lookup column and the return column. Always lock with $ signs when copying down. |
| col_index_num | Required | The column number within table_array to return, counting from 1 on the left. |
| range_lookup | Required | Always use FALSE for exact match. |
| IFERROR argument: value_if_error | Required | What to show when VLOOKUP returns any error. Use a text string for a message, 0 for numeric contexts, or an empty string to leave the cell blank. |
How it works
IFERROR evaluates VLOOKUP first and checks whether the result is any kind of error. If VLOOKUP finds a match it returns the value and IFERROR passes it through unchanged. If VLOOKUP cannot find the lookup value it returns #N/A, which IFERROR intercepts and replaces with whatever you put in the value_if_error argument. The substitution is seamless — the cell displays your fallback as though VLOOKUP had returned it directly. One thing to be aware of: IFERROR catches all error types, not just #N/A. A genuine formula mistake such as a column index larger than the table width returns #REF! — IFERROR swallows that too and shows your friendly fallback instead of the error, which makes formula bugs harder to spot. For lookup-only error handling that lets other errors remain visible, use IFNA instead. For files in Excel 2021 and newer, the XLOOKUP function handles not-found natively via its fourth argument, removing the need for a wrapper function entirely.
Examples
1
Look up each product's price from a reference table, showing Not found for any code that does not appear in the list.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Price | Code | Product | Price |
| 2 | PRD-003 | =IFERROR(VLOOKUP(A2,$D$2:$F$11,3,FALSE),"Not found") | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-099 | Not found | PRD-002 | USB Keyboard | 45.99 |
| 4 | PRD-005 | 12.99 | PRD-003 | Monitor Stand | 89.50 |
| 5 | PRD-004 | Laptop Bag | 59.99 | ||
| 6 | PRD-005 | HDMI Cable | 12.99 |
Row 2: 89.50
— PRD-003 is found in the reference table and 89.50 is returned from the Price column.
PRD-099 does not exist in the table so VLOOKUP returns #N/A. IFERROR intercepts it and displays Not found instead. Without IFERROR, that cell would show a red error that breaks any SUM or report referencing it.
2
Look up each employee's department by ID. Return No record for any ID not in the HR table.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Emp ID | Department | Emp ID | Department |
| 2 | EMP-002 | =IFERROR(VLOOKUP(A2,$D$2:$E$7,2,FALSE),"No record") | EMP-001 | Engineering |
| 3 | EMP-999 | No record | EMP-002 | Marketing |
| 4 | EMP-004 | Finance | EMP-003 | Sales |
| 5 | EMP-004 | Finance | ||
| 6 | EMP-005 | HR |
Row 2: Marketing
— EMP-002 is found in the HR table and Marketing is returned. EMP-999 does not exist so IFERROR shows No record instead of #N/A.
Locking the table range with $ signs — $D$2:$E$7 — means the formula copies down the column without the range shifting. Always lock lookup table references when dragging a VLOOKUP formula.
3
Use 0 as the IFERROR fallback when the price column feeds into a SUM so missing codes contribute zero rather than breaking the total.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Price | Code | Product | Price |
| 2 | PRD-001 | =IFERROR(VLOOKUP(A2,$D$2:$F$6,3,FALSE),0) | PRD-001 | Wireless Mouse | 29.99 |
| 3 | PRD-099 | 0 | PRD-002 | USB Keyboard | 45.99 |
| 4 | PRD-003 | 89.50 | PRD-003 | Monitor Stand | 89.50 |
| 5 | PRD-005 | 0 | PRD-004 | Laptop Bag | 59.99 |
| 6 | PRD-005 | HDMI Cable | 12.99 |
Row 2: 29.99
— PRD-001 is found and 29.99 is returned. PRD-099 and PRD-005 are missing from the reference table — IFERROR returns 0 for both so a SUM of the Price column gives the correct total without errors.
Use a text fallback like Not found for display columns and a numeric fallback like 0 for calculation columns. A text fallback in a price column breaks SUM and AVERAGE; a numeric 0 keeps aggregations working correctly.
Common use cases
1. Display Not found instead of #N/A in a product price lookup column used in a customer-facing report
=IFERROR(VLOOKUP(A2,$D:$F,3,FALSE),"Not found")
2. Return 0 for missing records when the result column feeds into a SUM or AVERAGE calculation
=IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),0)
3. Leave the cell blank when a lookup code has no match, to keep the sheet clean without visible errors
=IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),"")
4. Provide a two-table fallback by nesting a second VLOOKUP as the IFERROR value when the primary table has no match
=IFERROR(VLOOKUP(A2,Primary!$A:$B,2,FALSE),VLOOKUP(A2,Backup!$A:$B,2,FALSE))
5. Suppress #N/A in a VLOOKUP column used inside an IF so the IF logic is not disrupted by lookup errors
=IF(IFERROR(VLOOKUP(A2,$D:$E,2,FALSE),"")="Active","Yes","No")
Common errors
IFERROR returns the fallback even when the code exists
The lookup value and the table column have different data types — the code is stored as a number in one place and as text in the other. VLOOKUP treats them as different values.
Fix: Check with ISNUMBER(A2) and ISNUMBER(D2). If one is text and the other is a number, convert them using VALUE or by formatting the column consistently.
Formula errors like #REF! are silently hidden
IFERROR catches all error types including genuine formula mistakes. A wrong column index or broken range reference will show the friendly fallback instead of the error.
Fix: Use IFNA instead of IFERROR when you only want to handle the not-found case — IFNA catches only #N/A and lets other errors surface.
Table range shifts when copying the formula down
The table_array reference is not locked with $ signs so it moves as the formula copies down, causing wrong results or errors.
Fix: Always lock the table range: $D$2:$F$11 rather than D2:F11. Press F4 after selecting the range in the formula bar to toggle the $ locks.
Tips and variations
Use IFNA instead of IFERROR for tighter error handling
IFERROR catches every error including genuine mistakes. IFNA catches only #N/A from not-found lookups, leaving other errors visible. I default to IFNA in production workbooks so formula bugs do not hide behind friendly messages.
=IFNA(VLOOKUP(A2,$D:$F,3,FALSE),"Not found")
Use 0 as the fallback for numeric columns and text for display columns
A text fallback like Not found in a price column breaks SUM and AVERAGE. A 0 fallback keeps calculations correct. Match the fallback data type to the column purpose.
=IFERROR(VLOOKUP(A2,$D:$F,3,FALSE),0)
Switch to XLOOKUP if your Excel version supports it
XLOOKUP handles the not-found case through its own fourth argument without a wrapper: =XLOOKUP(A2,D:D,F:F,"Not found"). The formula is shorter and cleaner. Use IFERROR with VLOOKUP only when the file needs to open in Excel 2016 or earlier.
=XLOOKUP(A2,D:D,F:F,"Not found")
Frequently asked questions
IFERROR catches the #N/A error that VLOOKUP returns when a value is not found and replaces it with any value you choose — a text message like Not found, a zero for numeric contexts, or an empty string to leave the cell blank. Without IFERROR, a missing lookup value makes the cell show a red #N/A error that breaks any formula that references it.
It is safe but slightly over-broad — IFERROR catches every error type, not just #N/A. If your VLOOKUP formula has a genuine mistake such as a broken range reference, IFERROR silently hides it. For tighter error handling, use IFNA instead, which catches only #N/A and lets other errors remain visible. For most everyday lookup work, IFERROR is the conventional and practical choice.
XLOOKUP has a built-in fourth argument for the not-found case, so you do not need IFERROR at all: =XLOOKUP(A2,D:D,E:E,"Not found") handles missing values natively. If your file must work in Excel 2019 and newer, XLOOKUP is cleaner. For files that need to work in Excel 2016 and earlier, IFERROR wrapped around VLOOKUP is the standard approach.
Yes — this is the two-table fallback pattern. Put a second VLOOKUP as the value_if_error argument: =IFERROR(VLOOKUP(A2,Table1!A:B,2,FALSE),VLOOKUP(A2,Table2!A:B,2,FALSE)). The first lookup runs against the primary table and only when it fails does the second lookup run against the backup. No helper columns needed.
The most common cause is a data type mismatch — the lookup value is a number but the lookup column stores those numbers as text, or vice versa. VLOOKUP treats 1000 as a number and "1000" as text differently, so it returns #N/A even though the value appears to be there. Use ISNUMBER to check the data type in both the lookup value and the lookup column and convert one to match the other.