To use IFERROR with VLOOKUP in Excel, wrap the lookup formula inside IFERROR and provide the value to show if the lookup produces an error. For example, with a lookup key in D2 and a two-column table in A2:B100, use =IFERROR(VLOOKUP(D2,$A$2:$B$100,2,FALSE),"Not found"); Excel returns the matching value or the words Not found if the formula errors.
What IFERROR and VLOOKUP do
VLOOKUP searches for a value in the leftmost column of a selected table and returns a value from another column in the same row. It is commonly used to match a product code, employee ID, or other key against a reference list. IFERROR checks the result of a formula: if the result is an error, it returns a replacement value you specify; otherwise, it returns the formula’s normal result.
Combining them is useful when a missing key would otherwise leave an error such as #N/A in a report. Instead, a cell can display a clear message, an empty-looking result, or another appropriate fallback. The wrapper does not repair the lookup or add missing data. It only controls what Excel displays when the formula returns an error, so it is important to choose a fallback that does not hide a meaningful problem.
Basic syntax and an exact-match example
VLOOKUP takes a lookup value, a table range, a column number within that range, and an optional match setting. In Microsoft 365 for Windows, the general pattern is VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The fourth argument should usually be FALSE for an exact match. Leaving it out makes VLOOKUP use approximate matching, which can return an unintended result if the first column is not arranged as required.
Suppose A2:C4 contains a small catalog: A2 is P-104, A3 is P-205, and A4 is P-310; the product names are in column B and prices are in column C. D2 contains P-205. With that layout, this formula returns USB-C hub if that is the name in B3:
With product codes in A2:A4, names in B2:B4, prices in C2:C4, and the requested code in D2, enter =IFERROR(VLOOKUP(D2,$A$2:$C$4,2,FALSE),"Not found"). If B3 contains USB-C hub, the result is USB-C hub; if D2 contains a code that is not in A2:A4, the result is Not found.
The number 2 means “return the second column of the selected range,” not worksheet column B in every possible layout. Here the range begins in A, so its second column is B. To return the price instead, use column number 3. The dollar signs keep the lookup range fixed if you copy the formula down a column; D2 remains relative and changes to D3, D4, and so on.
How to build the formula
Build the lookup first, then add the error handling. A practical sequence is:
- Identify the value to search for, such as the code in D2.
- Select a table range whose leftmost column contains that value.
- Count across the selected range to find the return column number.
- Set the match argument to FALSE when you need an exact match.
- Wrap the VLOOKUP in IFERROR and choose a fallback that makes sense for the worksheet.
- Test one key that exists and one that does not, then copy the formula to other rows if needed.
For instance, if the same catalog has prices in the third column, the layout is still codes in A2:A4, names in B2:B4, prices in C2:C4, and the requested code in D2. Use =IFERROR(VLOOKUP(D2,$A$2:$C$4,3,FALSE),"Not found"). For P-205, if its price in C3 is 29.99, the formula returns 29.99. This example also shows why checking the column index matters: a valid lookup can return the wrong field if the index is one column off.
Choosing the fallback value
The fallback should help someone interpret the result, rather than merely make the sheet look tidy. Common options include:
- A message: Use a phrase such as Not found when a missing key should be investigated.
- An empty text result: Use two quotation marks with nothing between them when an error should display as a blank cell.
- A numeric default: Use a number only when it has a clear meaning in the worksheet; a zero can be mistaken for a real measured value.
With codes in A2:A100, return values in B2:B100, and the search value in D2, this version displays a blank-looking result for any error: =IFERROR(VLOOKUP(D2,$A$2:$B$100,2,FALSE),""). The formula returns the matched value when successful and an empty text string when it encounters an error. That is not the same as deleting the formula or making the cell truly empty. Other formulas, charts, filters, and exports may treat an empty text result differently from a genuinely empty cell, so use it deliberately.
Also note that a zero returned by a successful lookup is not an error. IFERROR will leave it as zero. Similarly, it does not replace a valid result that happens to be an empty string or an unexpected value. If those cases need special handling, add a separate test rather than expecting IFERROR to recognize them.
IFERROR or IFNA?
VLOOKUP commonly returns #N/A when it cannot find an exact match. IFERROR catches that error, but it also catches other formula errors, including #REF! and #VALUE!. A bad column number, for example, can produce #REF!; IFERROR may then show Not found even though the actual problem is a broken formula setup.
IFNA is a narrower option: it replaces #N/A while allowing other errors to remain visible. If the only expected problem is a missing match, this can make troubleshooting clearer. With codes in A2:A100, return values in B2:B100, and the requested code in D2, use =IFNA(VLOOKUP(D2,$A$2:$B$100,2,FALSE),"Not found"). A missing code returns Not found, but a #REF! caused by an invalid column number is still shown. IFNA and IFERROR are both available in modern Excel; for wider compatibility with older Excel workbooks, check which versions your recipients use before relying on a function.
Choose IFERROR when the goal is to substitute for any error from the whole expression. Choose IFNA when only a missing-match result should be softened and other errors ought to remain conspicuous. Neither function validates the source table, confirms that a code is unique, or explains why a lookup failed.
Common problems and how to troubleshoot them
If the formula returns the fallback for a value you believe is present, inspect the lookup data before changing the wrapper. The error may be the useful clue. Check these frequent causes:
- Wrong lookup column: VLOOKUP searches only the leftmost column of its table range. If codes are in B but the range starts in A, select a range beginning in B or use another lookup approach.
- Wrong column index: Count columns from the first column of the table range, not from the worksheet’s column letters. An index larger than the range can produce #REF!.
- Approximate matching: Confirm that the fourth VLOOKUP argument is FALSE. Without it, Excel uses approximate matching, which has ordering requirements and may not behave like an exact search.
- Different underlying values: A code stored as text may not match a code stored as a number. Extra spaces or inconsistent punctuation can also prevent a match. Compare the source and lookup values and standardize them when appropriate.
- Duplicate keys: VLOOKUP returns the first matching row it encounters. If a key appears more than once, IFERROR will not flag the ambiguity.
- Moved or changed data: Check that the range still includes the full table and that the requested return column is the intended one.
A useful debugging habit is to temporarily remove IFERROR and read the underlying error. If you see #N/A, investigate the match; if you see #REF!, review the range and column index. After correcting the cause, decide whether a fallback is still useful. Otherwise, a broad IFERROR wrapper can turn an assortment of genuine faults into the same harmless-looking message.
Copying the formula and maintaining the table
When applying a formula to many rows, lock the reference table with dollar signs and leave the lookup cell relative. For example, with source keys in A2:A100, return values in B2:B100, and lookup keys beginning in D2, use =IFERROR(VLOOKUP(D2,$A$2:$B$100,2,FALSE),"Not found"). Fill it down, and Excel changes D2 to D3, while the source range remains A2:B100.
If the list grows, a fixed range may not include newly added rows. You can update the range, or turn the source list into an Excel table using Insert > Table and build formulas around the table’s columns. Structured references can make a formula easier to read and can expand as table rows are added, but the exact formula depends on the table and column names. Check that the lookup column remains the first column of the VLOOKUP table array.
These formulas use commas between arguments, as in the examples. Excel’s argument separator can depend on regional settings; some installations expect semicolons instead. If Excel rejects a formula copied from another locale, check the separator setting rather than assuming the function name is wrong. The formula behavior is the same in current Excel for Windows and Mac, although ribbon details and regional separators can differ. Excel for the web also supports these common functions.
When another lookup method may be better
VLOOKUP is straightforward when the lookup key is at the left edge of a table and the return field is to its right. It is less flexible when the desired return column is to the left of the key, or when inserting columns makes a hard-coded column index difficult to maintain.
XLOOKUP can search in one range and return from another, including a range to the left, and it has a built-in argument for a value to return when there is no match. It is available in Microsoft 365 and Excel 2021 or later, but not in some older Excel releases. If a workbook must work with older versions, VLOOKUP with IFERROR or IFNA may be a more compatible choice. For either approach, make sure the fallback does not hide data-quality issues that users need to notice.
Frequently Asked Questions
What does IFERROR with VLOOKUP do?
It returns the VLOOKUP result when the lookup succeeds and a value you specify when the formula returns an error. It changes the displayed result; it does not create a missing record or fix the cause of the error.
How do I show blank instead of #N/A?
With lookup values in D2, keys in A2:A100, and results in B2:B100, use =IFERROR(VLOOKUP(D2,$A$2:$B$100,2,FALSE),""). This replaces any error with an empty text string, not only #N/A. Use IFNA instead if you want other errors to stay visible.
Why does my formula say Not found when the value exists?
Check that the key is in the first column of the selected range, that the match argument is FALSE, and that the values have matching types and characters. Look for spaces, text-versus-number differences, or a range that stops before the matching row.
Does IFERROR hide every kind of problem?
It replaces errors produced by the expression it wraps, not every incorrect result. It can hide errors such as #REF! as well as #N/A, but it will not catch a wrong value returned without an error or identify duplicate keys.
Should I use IFERROR or IFNA with VLOOKUP?
Use IFNA when the intended fallback is specifically for a missing match and you want other errors to remain visible. Use IFERROR when you intentionally want the same fallback for all errors produced by the lookup expression.
Related
- Excel Superscript Shortcut: Apply Superscript to Text
- Subscript vs Superscript in Excel: What’s the Difference?
- Subscript in Excel: Shortcut Key and How to Apply It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF Not Certain Text: How to Count Cells That Don’t MatchTo count cells that do not contain a specific text string in Excel, use the COUNTIF function with…
- Excel Percentage Difference Formula: How to CalculateFor the percentage difference between two nonnegative values in Excel, use =ABS(B2-A2)/AVERAGE(A2,B2), then format the result as a…
- VLOOKUP in Excel: Practical Tips for Reliable ResultsFor a reliable VLOOKUP in Excel, put the value you want to find in the first column of…
- SUMIFS in Excel with Dates: Examples and TipsTo sum values for dates in Excel, use SUMIFS with the amount range first and the date range…
- Sort an Entire Excel Spreadsheet by One ColumnTo sort an entire Excel spreadsheet by one column, click a cell in your data, choose Data >…
- Excel SUMIFS: Sum Values That Meet Multiple CriteriaTo sum values in Excel when several conditions must be true, use SUMIFS. For example, with regions in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.