Yes. With lookup IDs in A2:A100, return values in C2:C100, and the value to search for in E2, wrap VLOOKUP in IFERROR like this: =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found"). Excel returns the matching value when it finds one and displays “Not found” if the formula produces an error.
What IFERROR does with VLOOKUP
VLOOKUP searches for a value in the first column of a table and returns a value from another column in the same row. When no matching value exists, an exact-match VLOOKUP normally returns #N/A. IFERROR lets you replace that error with a message, a blank-looking result, or another value that makes sense for your worksheet.
The structure is IFERROR(value, value_if_error): Excel evaluates the first argument, then uses the second argument if the first produces an error. In the example above, VLOOKUP is the first argument and “Not found” is the fallback. If the lookup succeeds, IFERROR passes the lookup result through unchanged; it does not alter the matched value.
In VLOOKUP, E2 is the search key, $A$2:$C$100 is the table, 3 tells Excel to return a value from the third column of that table, and FALSE requests an exact match. The dollar signs keep the table range fixed if you copy the formula to other cells. The column number is counted from the table’s left edge, not from the worksheet’s column A.
A worked example
Suppose A2:C4 contains three records: ID 104 with a balance of 125, ID 105 with a balance of 90, and ID 106 with a balance of 210. Put 105 in E2. With those IDs in A2:A4 and balances in C2:C4, the same table layout in A2:C4, and the search value in E2, use:
=IFERROR(VLOOKUP(E2,$A$2:$C$4,3,FALSE),"Not found")
The result is 90 because ID 105 is in the first column of the table and its balance is in the third. If E2 contains 110 instead, the exact-match lookup finds no ID 110 and returns #N/A; IFERROR displays “Not found” instead. This example also shows why the lookup column must be at the left of the selected table range. A VLOOKUP cannot search column C and return a value from column A.
For a formula copied down a list, keep the data range absolute and let the lookup cell move by row. For example, $A$2:$C$100 stays in place while E2 becomes E3, E4, and so on. Check that the table range includes every relevant row; otherwise, a real record below row 100 will appear to be missing.
Choose a useful fallback
“Not found” is often clearer than a raw #N/A, especially in reports read by people who do not work with formulas. A blank-looking fallback can make a report less cluttered, but it may also conceal an incomplete lookup. Choose the response based on what users should do when a match is missing.
- Use a clear message such as “Not found” when someone should check the key or source data.
- Use a blank-looking result when an unmatched item is expected and the surrounding layout is easier to read without an error.
- Use a label such as “Review” when a missing match needs follow-up but should not be confused with a technical formula problem.
With lookup IDs in A2:A100, return values in C2:C100, and the search key in E2, this version displays an empty string if the lookup returns an error:
=IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"")
The cell looks blank, but it is not truly empty: it contains a formula whose result is a zero-length text string. That distinction can affect later formulas, charts, filters, and checks for empty cells. Also, IFERROR only uses its fallback when the formula errors. If VLOOKUP finds a matching row whose return cell is genuinely empty, the returned result may appear as 0; IFERROR does not treat that successful lookup as an error.
When IFNA may be the better choice
IFERROR catches any error produced by its first argument, not just “no match.” That breadth is convenient, but it can hide problems unrelated to missing data. For example, a wrong column index can cause #REF!, while an invalid argument or an issue in a referenced formula can cause another error. If every error becomes “Not found,” the displayed result may suggest the key is absent when the formula itself is broken.
When the only error you want to replace is #N/A, IFNA is more specific. With lookup IDs in A2:A100, return values in C2:C100, and the search key in E2, use:
=IFNA(VLOOKUP(E2,$A$2:$C$100,3,FALSE),"Not found")
IFNA returns “Not found” for #N/A but lets other errors remain visible, making it easier to spot a bad column number or another formula issue. In current Microsoft 365 Excel, both IFERROR and IFNA are available. Pick IFERROR when any error should be handled; pick IFNA when only a failed match should be handled.
Common VLOOKUP problems to check
If the formula shows the fallback unexpectedly, do not assume the record is absent. First check the lookup range and the key. VLOOKUP exact match is strict about the value being searched: a number stored as text may not behave like the same digits stored as a number, and leading or trailing spaces can make text keys differ. Verify both the source value and the lookup value rather than masking the result with a different fallback.
- Wrong first column: The lookup key must be in the first column of table_array. Select a range that starts with the key column.
- Wrong return-column number: Count columns from the first column in the selected range. A number larger than the table width can cause #REF!.
- Approximate match by accident: Use FALSE as the fourth argument when you need an exact match. If that argument is omitted, VLOOKUP defaults to approximate matching, which is unsuitable for many ID lists.
- Range does not include the record: Expand the table range or use an Excel table that grows as records are added.
- Duplicate keys: VLOOKUP returns the first matching row it encounters. IFERROR does not detect duplicates or choose the most recent record.
Exact-match VLOOKUP is commonly the right choice for product codes, employee IDs, and customer numbers. Approximate matching has legitimate uses, such as assigning a value to a range of thresholds, but it relies on data arranged appropriately and should be chosen deliberately. For ordinary lookups, explicitly writing FALSE makes the intended behavior easy to see.
How to troubleshoot without hiding the cause
During setup, temporarily remove IFERROR so the worksheet shows the original error. If the result is #N/A, investigate the key, data type, spaces, and lookup range. If it is #REF!, check the return-column number and range width. If another error appears, fix that cause rather than automatically labeling the record “Not found.” Once the lookup works as intended, add the chosen fallback.
You can also inspect the formula in the formula bar and confirm that the range begins with the lookup column, the return index points to the intended column, and FALSE is present. If the same key appears more than once, compare the source rows directly: the formula returns the first match, so a fallback cannot resolve ambiguity in the source data.
In workbooks shared across computers, formula argument separators can depend on regional settings. The formulas here use commas, as in standard English-language Excel syntax. If Excel rejects an otherwise correct formula on a computer configured for a different list separator, that installation may require semicolons between arguments. Do not change the quotation marks or parentheses; the separators are the part that can vary by locale.
IFERROR, IFNA, or XLOOKUP?
IFERROR and IFNA are wrappers around an existing lookup: VLOOKUP performs the search, while the outer function decides what to show when an error occurs. XLOOKUP can include a not-found response as one of its own arguments, and it can return values from a column to either side of the lookup column. That can avoid VLOOKUP’s left-to-right limitation and its manually counted return-column index.
For example, with IDs in A2:A100, balances in C2:C100, and the lookup value in E2, Microsoft 365 Excel can use:
=XLOOKUP(E2,A2:A100,C2:C100,"Not found")
This returns 90 for ID 105 in the worked example and “Not found” if there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel versions such as Excel 2019. VLOOKUP remains useful when a workbook must support older versions or when an existing workbook already uses it. Before changing a shared file to XLOOKUP, consider the Excel versions its recipients use.
Frequently Asked Questions
How do I return a blank instead of #N/A with VLOOKUP?
Wrap VLOOKUP in IFERROR and use an empty text string as the fallback. With IDs in A2:A100, return values in C2:C100, and a search key in E2, the formula is =IFERROR(VLOOKUP(E2,$A$2:$C$100,3,FALSE),""). The displayed result is blank when the lookup errors, but the cell still contains a formula.
Does IFERROR only hide #N/A?
No. IFERROR handles any error returned by its first argument, including errors such as #REF! as well as #N/A. If you want to replace only a missing-match error and keep other errors visible, use IFNA.
Why does VLOOKUP say “Not found” when the value is in the list?
Common causes include a number stored as text, extra spaces, a range that stops before the matching row, or a lookup range whose first column is not the key column. Compare the underlying values and test the VLOOKUP without IFERROR to reveal the original result.
Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column of its selected table and returns data from a column to its right. In Microsoft 365 or Excel 2021 and later, XLOOKUP can return from a column on either side; an INDEX and MATCH approach is another option for older versions.
Should I use FALSE or TRUE in VLOOKUP?
Use FALSE for an exact match, which is the usual choice for IDs and named items. TRUE requests approximate matching and requires data ordered for that behavior. Writing FALSE explicitly helps prevent an accidental approximate lookup.
Related
- Excel Data Modeling: How It Works and When to Use It
- Excel Data Modelling: Tables, Relationships and Pivots
- Excel Get Data From a Semantic Model: Connect and Analyze
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Sort Excel by Color: Cells, Font Colors, and IconsTo sort Excel rows by color in Microsoft 365 for Windows, select your data, choose Data > Sort,…
- Excel Named Range Sort: Sort Data or Name DefinitionsIn Excel, a named range is a label for a cell or group of cells, not a separate…
- Excel Consolidate Blank Cells Into One Cell With TEXTJOINYes—if by “blank wells” you mean blank cells between values, Excel can combine the nonblank entries into one…
- XLOOKUP in Excel: Compare Two Lists and Find MatchesYes. In Excel, XLOOKUP can compare a value in one list with another and return the matching item…
- Excel Formula for Time Clock Hours In and Out by ShiftFor clock-in time in A2 and clock-out time in B2, enter =MOD(B2-A2,1)*24 to return the shift length as…
- COUNTIF Excel Criteria Examples and How to Use ThemExcel’s COUNTIF function counts cells that meet one condition. With A2:A9 containing Paid, Pending, Paid, Overdue, paid, Pending,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.