Excel VLOOKUP Examples With IFERROR and Error Handling

Errors & fixes · Updated November 19, 2026

To handle a VLOOKUP value that is not found, wrap an exact-match VLOOKUP in IFNA and provide a useful fallback, such as “Not found.” Use IFERROR instead when you deliberately want to replace any formula error, not just a missing match; the examples below show both choices and when each is appropriate.

How VLOOKUP works and why errors appear

VLOOKUP searches down the first column of a table range for a value, then returns a value from another column in the same row. Its arguments specify the lookup value, the table range, the number of the column to return, and whether to use an approximate or exact match. That column number is counted from the left edge of the range, not from the worksheet’s column letters.

For most lists of names, product codes, or account numbers, an exact match is the safe choice. In Excel, set the final argument to FALSE. If you omit that argument, VLOOKUP uses approximate matching, which can return an unexpected result when the first table column is not sorted appropriately. A missing exact match commonly produces #N/A, while a return-column number larger than the table width produces #REF!. A malformed formula or an incompatible value can produce other errors.

Error handling should make a result easier to interpret without concealing problems that need attention. A clear “Not found” label is useful when an item may legitimately be absent. A generic blank can be confusing if users cannot tell whether the source value was missing, the formula was broken, or the returned cell was empty.

Basic exact-match VLOOKUP example

Suppose A2 contains a product code, and D2:F100 contains a product table: column D has product codes, E has descriptions, and F has prices. If A2 contains P-104, and that row’s price is 18.5, the following formula returns 18.5. The range starts in D, so column F is the third column of the lookup range.

With the lookup code in A2 and product codes, descriptions, and prices in D2:F100, use:

=VLOOKUP(A2,D2:F100,3,FALSE)

The result for P-104 is 18.5, which you can format as currency using the cell’s number format. If P-104 is not present, the result is #N/A. That is Excel’s indication that it did not find the requested exact match; it does not necessarily mean the formula itself is invalid.

Show a friendly message with IFNA

IFNA replaces only the #N/A error with the value you specify. This is often the most precise option for a lookup: it handles the expected “not found” case while leaving other errors visible for investigation. For example, if someone later changes the return-column number to one outside the table, #REF! will still appear instead of being disguised as a missing product.

With A2 holding the product code and D2:F100 holding the three-column product table, use:

=IFNA(VLOOKUP(A2,$D$2:$F$100,3,FALSE),"Not found")

If A2 contains P-104 and the matching row’s price is 18.5, the formula returns 18.5. If there is no exact match, it returns the text Not found. The dollar signs keep the table range fixed if you copy the formula to other rows; the lookup reference A2 changes to A3, A4, and so on.

Choose wording that suits the workbook. “Not found” may be right for a user-facing report, while “Check product code” may prompt someone to correct an input. If downstream calculations expect a number, remember that a text message is not a numeric zero. Mixing text and numbers can affect sums, charts, and later formulas.

When IFERROR is useful—and when it is too broad

IFERROR returns a chosen alternative for any error in its first argument, including #N/A, #REF!, #VALUE!, and #DIV/0!. It can be appropriate when every error should lead to the same safe result, or when you are building a presentation layer that should not display raw error values. However, using it automatically around every lookup may hide a damaged range or an incorrect column index.

With A2 as the lookup code and D2:F100 as the product table, this version displays a review message for any error:

=IFERROR(VLOOKUP(A2,$D$2:$F$100,3,FALSE),"Check lookup")

For P-104 with a price of 18.5, the result is 18.5. For a missing code, it returns Check lookup. It would also return Check lookup if the formula used an invalid return-column index, so it is less diagnostic than IFNA. During setup or troubleshooting, leaving unexpected errors visible can make the underlying cause easier to spot.

A useful rule is to use IFNA when only a missing match is an expected condition, and IFERROR when all errors are intentionally treated alike. Avoid changing every error to zero unless zero is genuinely the correct business meaning. A zero price, for example, could be mistaken for a real price rather than a failed lookup.

Handle blank inputs without hiding missing matches

A lookup cell may be empty because a user has not entered a code yet. Without an extra check, the formula may attempt a lookup anyway and show a result or an error that distracts from the unfinished input. An outer IF can return a blank until the user supplies a code, while an inner IFNA still reports a missing code after one is entered.

With the input in A2 and the product lookup table in D2:F100, use:

=IF(A2="","",IFNA(VLOOKUP(A2,$D$2:$F$100,3,FALSE),"Not found"))

When A2 is empty, the result is an empty text string, so the cell appears blank. When A2 contains P-104 and its price is 18.5, the result is 18.5; when it contains a code absent from the table, the result is Not found. This checks whether the input is blank, not whether the lookup’s return cell is blank. A blank source cell and a missing lookup are different cases, so decide how the former should be represented if it matters to your report.

Approximate-match lookups need a different safeguard

VLOOKUP can also find the closest lower threshold, which is useful for bracket tables such as shipping bands or rating cutoffs. For approximate matching, the first column of the lookup range must be sorted in ascending order. Excel returns the largest value in that column that is less than or equal to the lookup value. If the table is not sorted correctly, the answer may be unreliable.

Suppose H2:I6 contains thresholds 0, 10, 20, 30, and 40 in column H, with corresponding rates 0%, 5%, 10%, 15%, and 20% in column I. If E2 contains 27, this formula returns 10%, the rate associated with the 20 threshold.

With the amount in E2 and ascending thresholds and rates in H2:I6, use:

=VLOOKUP(E2,$H$2:$I$6,2,TRUE)

TRUE explicitly requests approximate matching. An amount below the smallest threshold may return #N/A, so consider whether that input should be blocked, flagged, or handled with an IFNA message. Do not switch an exact product-code lookup to TRUE as a way to avoid #N/A; approximate matching changes the meaning of the search and can return a different record.

Common VLOOKUP errors and ways to diagnose them

Before adding a broad error wrapper, check the formula and the source data. Many apparent lookup failures come from small inconsistencies rather than a need for a more complicated formula. Work through these common causes:

  • #N/A: Confirm that the lookup value exists in the first column of the selected range. Check for leading or trailing spaces, spelling differences, and numbers stored as text in one place but as numbers in another.
  • Wrong result: Confirm that the last argument is FALSE for an exact lookup. For approximate matching, verify that the first column is sorted ascending and that the returned threshold is appropriate.
  • #REF!: Make sure the return-column index does not exceed the number of columns in the table range. For D2:F100, valid return-column indexes are 1, 2, and 3.
  • Wrong column returned: Count from the left edge of the table array. If the range starts at D, then D is 1, E is 2, and F is 3.
  • Results change after copying: Lock the table range with dollar signs while leaving the row-specific lookup cell relative, as in the earlier examples.
  • Unexpected duplicate match: VLOOKUP returns the first matching row it encounters. Check whether the key appears more than once and decide whether the first occurrence is actually the desired record.

Exact matching does not automatically make text data identical. For example, a code with an extra space may look the same on screen but fail to match. Inspect the source values and clean or standardize them where appropriate. Be cautious about changing source data in bulk: whitespace or leading zeros can be meaningful in identifiers, even though they are not meaningful in ordinary numbers.

VLOOKUP limits and alternatives

VLOOKUP can return values only from columns to the right of its lookup column. If the field to return is to the left, you must rearrange the table or choose another approach. Its numeric column index can also become incorrect if someone inserts or rearranges columns inside the lookup range and does not update the formula.

In Microsoft 365, XLOOKUP is an alternative that supports a separate not-found result and can return data from either side of the lookup column. For example, with product codes in D2:D100 and prices in F2:F100, and the requested code in A2, use:

=XLOOKUP(A2,D2:D100,F2:F100,"Not found")

For P-104 with a price of 18.5, this returns 18.5; for an absent code, it returns Not found. 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 compatibility with older workbooks or users is important. The lookup formulas shown here work in Excel for Microsoft 365 on Windows; their syntax is the same in current Excel for Mac and Excel for the web. Some regional settings use semicolons instead of commas as formula separators, so follow the separator convention used by your Excel installation.

Frequently Asked Questions

How do I make VLOOKUP return blank instead of #N/A?
Wrap the lookup in IFNA and use an empty text string as the fallback. This makes a missing match appear blank, but it also makes it harder to distinguish missing data from an intentionally blank result. A clear label is often better in worksheets used for checking or data entry.

Should I use IFERROR or IFNA with VLOOKUP?
Use IFNA when the expected problem is that no match exists; it leaves other errors visible. Use IFERROR only if replacing all formula errors with one fallback is intentional. A broad fallback can hide a broken range or an invalid return-column index.

Why does VLOOKUP find a value in one table but not another?
Compare the lookup value and the first column of each table for hidden spaces, inconsistent spelling, and text-versus-number differences. Also check that the lookup column is actually the first column of the selected table range and that exact matching is specified when appropriate.

Does VLOOKUP return the first or last duplicate?
With the usual top-to-bottom search, VLOOKUP returns the first matching row in the lookup column. If duplicates exist, confirm that the first row is the one you want; otherwise, make the key unique or use a method designed for the required result.

Can VLOOKUP search from right to left?
No. The lookup values must be in the leftmost column of its table range, and the return column must be to the right. XLOOKUP is an option in Microsoft 365 and Excel 2021 or later when you need to return a value from a column on either side.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.