IFERROR VLOOKUP

What this does

Combining IFERROR with VLOOKUP is the standard pattern for lookup formulas that must handle missing values gracefully. VLOOKUP returns #N/A when the search value is not found in the lookup column; wrapping it in IFERROR intercepts that error and returns a friendly fallback value instead — an empty string, zero, or a descriptive message like "Not found". This keeps downstream formulas and reports from propagating the error. IFNA() is a more precise alternative that catches only #N/A errors (not formula syntax errors), but IFERROR(VLOOKUP(…), fallback) is the most widely-used pattern and works in all Excel versions that support VLOOKUP. Keep the inputs visible and clearly labelled and the whole thing stays auditable — anyone who opens the file later, including you, can see at a glance exactly what feeds the result and change one assumption without hunting through the formula. Treat “iferror vlookup” as a small repeatable workflow rather than a one-off click you hope to remember next time. Use a small test block before the live file, so any surprise in the affected cells shows up while it is still harmless. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That turns a data step that keeps your analysis trustworthy into a method you can reuse, explain, and defend when the workbook leaves your screen.

A worked example

Scenario: column A has product IDs, column B has prices, and cell E2 contains a product ID you are looking up. The formula =IFERROR(VLOOKUP(E2,A:B,2,FALSE),"Not found") searches column A for the value in E2 and returns the corresponding price from column B. If E2 contains a product ID not in column A, VLOOKUP would return #N/A; IFERROR intercepts it and shows "Not found" instead. To debug a missing match: temporarily remove IFERROR to see the raw #N/A, then use =COUNTIF(A:A,E2) to confirm whether the value exists in the lookup range. Wrap every production VLOOKUP in IFERROR (or IFNA) before sharing the workbook. A bare VLOOKUP that hits a missing key will cascade #N/A through every dependent cell, breaking totals, conditional formats, and charts. The one-second effort of adding IFERROR around the formula prevents those downstream failures and keeps the workbook usable even when input data is incomplete. One habit worth forming early: name the cells that hold your inputs, so the formula reads in plain language instead of a string of cell addresses. A reviewer — or you in three months — can then follow the logic without decoding what B7 and D2 were supposed to mean, which is most of what makes a sheet maintainable.

In Google Sheets

Some functions on this page are newer additions to Excel: they are in current Microsoft 365 and Excel for the web, while older perpetual Excel versions return #NAME?. Google Sheets maintains its own function list, so confirm each function exists there before relying on the same formula. Nothing on this page is behind a login or a download: the exact answer is at the top, and the detail below it is there for when you need it. That is the whole promise here — the answer first, and just enough context to make it stick. Here is the takeaway for “iferror vlookup”: copy the answer if you are busy, but if you have a spare few minutes, rebuild the example in Excel yourself with the tool above open beside it. That single pass — type it, run it, watch the result move when you change an input — is what turns a formula you found into a technique you trust. Keep your inputs labelled and referenced, never hard-coded, and the same sheet stays correct and auditable as it grows. Done that way, you will not need to look this up again, and you will be the person others ask.

Common mistakes

• Using IFERROR to hide a configuration error — if VLOOKUP returns #REF! or #VALUE! (wrong column index, wrong range) IFERROR silently hides the bug. Test the raw VLOOKUP first before adding IFERROR.
• Setting the fallback to 0 when the result feeds into an average or ratio — zeros silently distort aggregates. Use "" or "N/A" for qualitative lookups, or IFNA to suppress only #N/A.
• Forgetting FALSE as the fourth argument — VLOOKUP with TRUE (approximate match) searches a SORTED column; on an unsorted column it returns wrong results without an error, and IFERROR never fires because there is no error to catch.

Frequently asked questions