To show a VLOOKUP result when it works and a message when it errors, use IF with ISERROR. If the lookup value is in E2 and the lookup table is in A2:C100, with the desired result in column C, enter =IF(ISERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE)),"Not found",VLOOKUP(E2,$A$2:$C$100,3,FALSE)). For most workbooks, the shorter IFERROR version is easier to read.
What “IF VLOOKUP is not error” means
The phrase usually describes a test: run VLOOKUP, check whether it produces an error, and choose what to display based on that result. ISERROR returns TRUE for any Excel error value and FALSE for a regular value. IF then uses that TRUE or FALSE result to select one of two outcomes. In the example above, a failed lookup displays “Not found”; a successful lookup displays the value VLOOKUP returns.
This is useful when a worksheet should show a readable message instead of an error such as #N/A. It can also be used to label results as “Found” or “Missing,” or to trigger another calculation only when a lookup succeeds. The key is to decide whether you want to return the actual lookup result or merely test whether the lookup succeeded. Those are related, but not identical, tasks.
The simpler option: IFERROR with VLOOKUP
If the goal is to return the VLOOKUP result unless it errors, IFERROR is usually the clearest choice. With the lookup value in E2, the table in A2:C100, and the result in column C, enter =IFERROR(VLOOKUP(E2,$A$$2:$C$100,3,FALSE),"Not found").
There is a typo risk in formulas when editing references, so check that the table reference above is exactly $A$2:$C$100 in Excel. The complete formula is =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). It returns the matched value from column C, or the text “Not found” if VLOOKUP returns an error. IFERROR handles any error generated by the expression, not just a missing match.
In a longer formula, that difference matters. A VLOOKUP error could mean the key is absent, but it could also point to a bad column number, a broken reference, or another problem. IFERROR will replace all of those errors with the same message. That makes a sheet look tidy, but can conceal a mistake; use it when the fallback is appropriate for every error you intend to catch.
Using IF with ISERROR to return a status
Sometimes the desired output is a status rather than the matched value. With IDs in A2:A100, a lookup ID in E2, and a table in A2:C100, enter =IF(NOT(ISERROR(VLOOKUP(E2,$A$$2:$C$100,3,FALSE))),"Found","Not found"). As with the earlier example, verify the reference is $A$2:$C$100; the complete formula is =IF(NOT(ISERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE))),"Found","Not found"). It displays “Found” when VLOOKUP returns a non-error value and “Not found” when it returns any error.
NOT reverses the TRUE/FALSE test from ISERROR. Without NOT, the IF test is true when VLOOKUP has an error; with NOT, it is true when VLOOKUP does not have an error. You can instead swap the two IF outcomes and leave NOT out. This status formula does not display the looked-up value—it only reports whether the lookup returned something other than an error.
A successful lookup can return an empty cell, which may appear blank in the result. ISERROR still treats that as a successful lookup because a blank is not an error. Therefore, “Found” means VLOOKUP did not return an error; it does not guarantee that the matched row’s result cell contains useful text or a number.
Set up VLOOKUP so the test is meaningful
Error handling cannot correct an incorrectly configured lookup. VLOOKUP searches the leftmost column of its table range and returns a value from a column to its right. Its third argument is the position within the selected range, not the worksheet’s absolute column number. For a range starting in column A, column C is the third column, which is why the examples use 3.
For ID or name matching, use an exact match by setting the fourth argument to FALSE. If the lookup ID is in E2 and the table is A2:C100, for example, the VLOOKUP portion is VLOOKUP(E2,$A$2:$C$100,3,FALSE). This searches for an exact match in the first column of the range. If no exact match exists, VLOOKUP returns #N/A, which the surrounding IFERROR or ISERROR test can handle.
Leaving out the fourth argument, or using TRUE, requests an approximate match. Approximate matching is intended for ordered ranges and depends on the lookup column being sorted appropriately. It can return a nearby result rather than an error when an exact key is absent, so an error test alone will not identify every incorrect match. For product codes, employee IDs, and other keys that should match exactly, FALSE is generally the safer setting.
When copying a formula down a column, the lookup value should normally adjust by row, while the lookup table should remain fixed. Dollar signs in $A$2:$C$100 lock the table reference. Without them, the table range may shift as the formula is filled down, producing unexpected errors or incorrect results.
Choosing what to show when the lookup fails
The fallback should tell the reader what happened without disguising important data problems. “Not found” is useful when an absent key is an expected possibility. A blank result may be preferable in a presentation sheet, but it can make a missing record hard to notice. A separate status column is often better for audit or reconciliation work because it preserves the distinction between the returned value and the lookup outcome.
With E2 as the lookup key and A2:C100 as the lookup table, a formula that displays a blank for an error is =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). The empty text between the quotation marks is an intentional blank-looking result. It does not delete data or make the lookup succeed; the underlying formula still returns an empty string when it encounters an error.
For troubleshooting, returning the error itself can be more informative than replacing it. If you choose a custom message, consider making it specific enough to guide the next step, such as “Check ID” or “No matching code.” Avoid a message that claims a record is missing if the table could instead be misconfigured. A fallback is a display decision, not a substitute for checking the lookup range and column index.
Common problems and what they indicate
- #N/A: VLOOKUP did not find an exact match. Check for spelling differences, extra spaces, mismatched number-versus-text values, or a key missing from the first column of the table.
- #REF!: The requested return-column number is greater than the number of columns in the selected table range, or a referenced range has been removed. Confirm the range and the third argument.
- #VALUE!: An argument may have an invalid type or value. Check that the column index is a positive number and that the formula arguments are in the intended order.
- A plausible but wrong result: The formula may be using approximate match, the wrong lookup column, or a duplicate key. Exact match prevents approximate results, but VLOOKUP still returns the first matching row it encounters.
- A blank result despite a match: The matching row’s return cell may be empty. ISERROR regards that as a non-error result, so a “Found” status can coexist with a blank displayed value.
One common misconception is that IFERROR means “if VLOOKUP does not find the value.” More precisely, it means “if this expression returns any error.” That distinction explains why it can hide a broken reference as well as #N/A. During development, remove the wrapper temporarily or inspect the source range so genuine formula errors do not go unnoticed.
Alternatives: IFNA and XLOOKUP
If you only want to replace a missing-match error, IFNA is narrower than IFERROR. With E2 as the key and A2:C100 as the table, enter =IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). It replaces #N/A with “Not found” but leaves other errors visible, which can make formula or reference problems easier to detect. Choose it when #N/A is the expected failure and other errors should remain apparent.
In Microsoft 365 and Excel 2021 or later, XLOOKUP can specify a not-found result directly. With keys in A2:A100, return values in C2:C100, and the lookup key in E2, use =XLOOKUP(E2,A2:A100,C2:C100,"Not found",0). The final 0 requests an exact match, and the fourth argument supplies the fallback text. Unlike VLOOKUP, XLOOKUP does not require a numeric column index and can return values from a column to the left or right of the lookup range.
VLOOKUP remains useful in older workbooks and is widely recognized, so changing functions is not always necessary. If a file must work in older Excel versions, check function compatibility before adopting XLOOKUP. Excel for the web and current Microsoft 365 versions support modern lookup functions, while some older perpetual releases may not. On Mac, formula behavior is generally the same; regional settings can affect whether Excel expects commas or semicolons between arguments.
A practical way to build and check the formula
- Confirm which column contains the lookup key. VLOOKUP searches only the leftmost column of the selected table range.
- Identify the return column and count its position within that range, starting at 1.
- Use FALSE for an exact match unless approximate matching is deliberately required and the data is prepared for it.
- Test the plain VLOOKUP first with one key you know is present and one you know is absent. This reveals whether the range and key types behave as expected.
- Add IFERROR, IFNA, or an ISERROR test only after confirming the underlying lookup is correct. Select the function based on which errors should be caught and what result users need to see.
In Excel for Microsoft 365 on Windows, enter the formula in the result cell and press Enter. If you need to fill it down, use the fill handle at the lower-right corner of the cell, or copy and paste the formula into the desired rows. The corresponding ribbon commands for copying are on Home > Clipboard; no special lookup setting is required. On Mac, the formulas work the same way, although keyboard shortcuts and some interface details differ.
Frequently Asked Questions
How do I show “Not found” instead of #N/A?
Wrap the exact-match VLOOKUP in IFNA if you only want to replace #N/A, or IFERROR if you want to replace all errors. Use a message that reflects the kind of failure you are catching.
What is the difference between IFERROR and ISERROR?
IFERROR returns the original result when there is no error and a fallback when there is one. ISERROR returns TRUE or FALSE, so it is useful inside IF when you need to choose a status or take different actions based on whether an error occurred.
Can I use IF with VLOOKUP to return the actual value?
Yes. Use IFERROR around VLOOKUP with the actual value as the successful result and your chosen fallback as the error result. If you use IF with ISERROR, put the VLOOKUP in both the test and the successful-result branch.
Why does my formula say “Found” when the result looks blank?
The matched row may have an empty return cell. A blank is not an error, so ISERROR reports that the lookup succeeded. Check the source cell or use a separate test if the returned value itself must be nonblank.
Does IFERROR only catch a value that VLOOKUP cannot find?
No. It catches any error in the expression, including errors other than #N/A. Use IFNA when you specifically want to handle a missing match while leaving other errors visible.
Why does VLOOKUP return the wrong row?
Check whether the formula uses TRUE or omits the match argument, whether the lookup column is the first column of the range, and whether the key appears more than once. For unique exact keys, use FALSE and verify the source values.
Related
- Excel Data Validation List Without a Cell Reference?
- Excel German Number Format: Commas, Dates, and Currency
- Excel General Number Format: How It Works and When to Use It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Formula Is Correct but Not Working? How to Fix ItIf a formula is correct but not working in Excel, first check whether the cell is formatted as…
- Excel Filter Not Working? Common Causes and Fixes GuideIf a filter in Excel is not working, first check that it covers the entire data range, that…
- VLOOKUP Not Working? Fix Formulas Showing in ExcelIf Excel displays the VLOOKUP formula itself instead of a result, first turn off Formulas > Formula Auditing…
- Excel Spill Error: Why It Happens and How to Fix ItTo fix Excel’s #SPILL! error, select the cell showing the error, identify the intended spill range, and remove…
- Excel FILTER Spill Error: Why It Happens and How to Fix ItAn Excel FILTER spill error means Excel cannot place the formula’s results in the cells where they need…
- VLOOKUP Not Working? Fix Excel Displaying the FormulaIf Excel displays =VLOOKUP(...) in a cell instead of a lookup result, first turn off Formulas > Formula…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.