The direct answer: wrap your VLOOKUP in IFERROR like this: =IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not Found"). This makes Excel return your own message (or a blank) instead of an ugly #N/A error when the lookup value isn’t found.
Why VLOOKUP Returns Errors
VLOOKUP is one of Excel’s most-used functions, but it’s also one of the most error-prone. The most common error is #N/A, which simply means “I couldn’t find the lookup value in the first column of your table.” That happens when you mistype a name, the data isn’t there yet, or the lookup column doesn’t contain an exact match (when using FALSE).
Other errors you might see: #REF! if your column index number is larger than the number of columns in the range, #VALUE! if the lookup value is wrong type, and #NAME? if you misspell the function. IFERROR catches all of these, not just #N/A.
How IFERROR Works
IFERROR takes two arguments: the formula you want to evaluate, and what to return if that formula produces any error. The syntax is:
=IFERROR(value, value_if_error)
For example, =IFERROR(1/0, "Cannot divide by zero") returns “Cannot divide by zero” because 1/0 is a #DIV/0! error. In our VLOOKUP case, the first argument is the entire VLOOKUP formula, and the second is whatever you want to show instead of an error.
Basic IFERROR with VLOOKUP Example
Assume you have a product list in cells A1:B10, with product IDs in column A and prices in column B. In cell D2 you type a product ID, and in E2 you want its price. The formula would be:
=IFERROR(VLOOKUP(D2, A:B, 2, FALSE), "Product not found")
If D2 contains “P123” and that ID exists in A1:A10, you get the price. If not, you get “Product not found” instead of #N/A. You can also use an empty string "" to show a blank cell, or 0 if you prefer a numeric default.
Using IFERROR with Approximate Match (TRUE)
When you use VLOOKUP with TRUE (or omit the fourth argument), Excel looks for the closest match that is less than or equal to the lookup value. This is common for tax brackets, commission tiers, or shipping rates. IFERROR still works the same way, but note that VLOOKUP with TRUE rarely returns #N/A unless the lookup value is smaller than the smallest value in the table. Example:
With a rate table in A1:B5 (income thresholds in A, tax rate in B), and income in D2:
=IFERROR(VLOOKUP(D2, A:B, 2, TRUE), "Below minimum")
If D2 is 50000 and the table starts at 0, you get the correct rate. If D2 is negative, you get “Below minimum”.
Common Mistakes and How to Avoid Them
- Forgetting the FALSE argument: If you want exact matches, always use FALSE. Otherwise you may get wrong results silently.
- Using IFERROR to hide real problems: IFERROR masks all errors, including ones that indicate broken formulas. Use it only when you expect and want to handle errors.
- Column index out of range: If your table is A:C and you ask for column 4, you get #REF!. IFERROR will catch it, but better to fix the column number.
- Lookup column not the first column: VLOOKUP always searches the first column of the range. If your ID is in column B, use a range like B:C.
Alternatives to IFERROR with VLOOKUP
In newer Excel versions (Excel 2021 or Microsoft 365), you can use XLOOKUP which has a built-in if_not_found argument. The syntax is:
=XLOOKUP(D2, A:A, B:B, "Not found")
This is cleaner and doesn’t require wrapping. Another alternative is IFNA, which only handles #N/A errors, leaving other errors visible. That’s useful when you want to catch only missing values:
=IFNA(VLOOKUP(D2, A:B, 2, FALSE), "Not found")
IFNA is available in Excel 2013 and later, so it’s a good middle ground.
IFERROR with VLOOKUP Across Sheets and Workbooks
You can reference another sheet or workbook. For example, if your lookup table is on a sheet named “Data”:
=IFERROR(VLOOKUP(D2, Data!A:B, 2, FALSE), "Not found")
For another workbook, include the file path in brackets: ='[Sales.xlsx]Data'!A:B. IFERROR works the same, but be aware that external links can slow down your workbook and may break if the source file is moved. Always keep both files in the same folder if possible.
Frequently Asked Questions
Can IFERROR return a blank cell instead of text? Yes, use "" as the second argument: =IFERROR(VLOOKUP(...), ""). This leaves the cell visually empty, which is handy for charts or further calculations.
Does IFERROR work with array formulas? Yes, IFERROR works with array formulas in modern Excel. For example, =IFERROR(VLOOKUP(D2:D5, A:B, 2, FALSE), "") will return an array of results. In older Excel, you’d need to enter it as an array formula with Ctrl+Shift+Enter.
Why does my VLOOKUP still show #N/A even with IFERROR? IFERROR only works if the formula itself is error-free. If you have a typo in the formula, like VLOKUP, you’ll get #NAME? which IFERROR can catch, but if the error is in the data (e.g., a cell contains text where a number is expected), IFERROR will still show the error because it’s not a formula error.
Is IFERROR slower than IFNA? IFERROR checks for all errors, so it does slightly more work, but the difference is negligible unless you have tens of thousands of formulas. For large datasets, IFNA might be marginally faster, but both are fine.
Can I use IFERROR with INDEX/MATCH instead? Absolutely. The same wrapping works: =IFERROR(INDEX(B:B, MATCH(D2, A:A, 0)), "Not found"). This is often a more flexible alternative to VLOOKUP because it can look to the left.
Related
- Excel Employee Rotation Schedule Template Guide
- Excel Employee Hourly Schedule Template: How to Build One
- Excel Date Minus Date to Get Months
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- VLOOKUP #REF! Error: Why It Happens and How to Fix ItA VLOOKUP #REF! error means the formula contains an invalid cell or range reference. The most common cause…
- Insert Option Greyed Out in Excel? How to Fix RowsIf Excel’s Insert option is greyed out when you want to add a row, first press Esc, check…
- Hyperlink Not Working in PowerPoint? Fix the Common CausesIf a hyperlink is not working in PowerPoint, first test it in Slide Show view: in editing view,…
- Excel Circular Reference Example and How to Fix ItAn Excel circular reference occurs when a formula refers to its own cell, either directly or through other…
- Excel Filter Shortcut Not Working? Causes and FixesIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. If nothing happens,…
- VLOOKUP Not Working? Causes and FixesIf VLOOKUP is not working, set its final argument to FALSE for an exact match, then check that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.