To show a useful result when an INDEX MATCH lookup fails, wrap the lookup in IFERROR, such as =IFERROR(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"Not found"). Use IFNA instead when you want to handle only a missing-match error, or use IF when the result depends on a condition such as whether the lookup cell is blank.
What “IF ELSE error” means in Excel
Excel does not have a function named “IF ELSE.” The phrase usually refers to choosing what a formula should return when a condition is true or false, or when a lookup produces an error. Excel’s IF function handles a true-versus-false test; IFERROR and IFNA handle errors. These functions can be combined with INDEX and MATCH to make a lookup more useful than a raw error message.
A raw INDEX MATCH formula returns a matching value when it finds one, but an unsuccessful exact match normally produces #N/A. That is not necessarily a spreadsheet failure: it may simply mean the entered ID is not in the list. A formula can replace that message with “Not found,” a blank, or an instruction to check the input. The important choice is whether to handle every error or only the expected missing-match case.
How INDEX and MATCH work together
MATCH searches a range and returns the position of a value within that range. INDEX uses a position to return a value from another range. For an exact lookup, the final argument of MATCH is 0. The lookup range and return range should cover corresponding rows, so the position returned by MATCH points to the intended result in INDEX.
For example, suppose A2:A6 contains IDs 1041, 1042, 1043, 1044, and 1045, while B2:B6 contains North, South, West, East, and Central. E2 contains the ID to find. With those cells in place, this formula returns West when E2 is 1043:
With IDs in A2:A6, regions in B2:B6, and the lookup ID in E2, enter:
=INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0))
The dollar signs keep the two data ranges fixed if you copy the formula down, while E2 changes to E3, E4, and so on. This pattern is useful when the lookup column is to the left of the result column, and it also lets you change the return range without moving the source data. MATCH returns the first matching position when duplicate IDs exist; it does not automatically return every matching row.
Use IFERROR to replace a failed lookup
IFERROR takes a value or formula to evaluate and a value to return if that evaluation produces an error. In the example below, an ID that is absent from A2:A6 displays “Not found” instead of #N/A. If E2 contains 1043, the returned result is West; if E2 contains 1099, the result is Not found.
With IDs in A2:A6, regions in B2:B6, and the lookup ID in E2, use:
=IFERROR(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"Not found")
IFERROR is convenient when a clean worksheet matters, especially in reports where raw errors could confuse readers. It can also return an empty string instead of a message. That looks like a blank cell, but the cell still contains a formula; functions and charts may treat it differently from a genuinely empty cell. Also, IFERROR catches all error types, not just #N/A. If the INDEX range is accidentally wrong or another problem occurs, the fallback can hide that problem too.
Choose between IFERROR, IFNA, and IF
Pick the function according to the situation rather than using a generic error wrapper automatically:
- IFERROR returns a fallback for any error in the formula. It is broad and simple, but can conceal errors that deserve investigation.
- IFNA returns a fallback only for #N/A. It is often a better fit when a missing exact match is expected but other errors should remain visible. IFNA is available in Excel 2013 and later, including Microsoft 365.
- IF tests a logical condition and chooses one result if it is true and another if it is false. It does not, by itself, catch an error produced by a lookup.
For example, IFNA preserves a #REF! error caused by an invalid reference, whereas IFERROR would replace it with the fallback text. That distinction makes IFNA a useful diagnostic choice. However, if the formula can produce errors other than #N/A that you intentionally want to suppress, IFERROR may be appropriate. Document the reason for a broad fallback when other people maintain the workbook.
With IDs in A2:A6, regions in B2:B6, and the lookup ID in E2, this narrower version returns “Not found” for #N/A but allows other errors to appear:
=IFNA(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"Not found")
Handle blank inputs and add an IF condition
A blank lookup cell can be confusing: the formula may search for a blank value, or display “Not found” even though the user has not entered anything yet. Use IF to check the input first, then run the lookup only when the input is present. This separates “waiting for an ID” from “ID entered, but no match exists.”
With IDs in A2:A6, regions in B2:B6, and the user’s entry in E2, this formula displays a prompt for a blank input and a separate message for an absent ID:
=IF(E2="","Enter an ID",IFNA(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"No match"))
Here, E2 blank returns “Enter an ID”; E2 equal to 1043 returns West; and E2 equal to 1099 returns “No match.” The nested IF is acting like a simple if/else decision: first test whether there is an input, then handle the lookup result. The order matters because it gives an empty entry a more informative response than a failed search.
Common causes of INDEX MATCH errors
When a formula shows an unexpected result, check the data and references before adding a broader error handler. Common causes include:
- The value is not an exact match. A trailing space or a different spelling can prevent a text lookup from matching. Numbers stored as text may also fail to match numeric values, even when they look identical on screen.
- The ranges do not line up. MATCH returns a position within its lookup range, so the INDEX return range should represent the corresponding rows in the same order. A shorter or offset return range can produce an error or an incorrect result.
- The lookup type is not exact. For an ordinary exact lookup, specify 0 as MATCH’s third argument. Omitting it invokes approximate-match behavior, which has different requirements and can return a surprising position.
- There are duplicate keys. MATCH finds the first matching item in the search range. If IDs should be unique, check the source data; if they are not unique, decide which occurrence is intended.
- The cell contains invisible characters. Extra spaces are common in imported text. TRIM can remove ordinary leading and trailing spaces, but some imported nonbreaking spaces require separate cleanup.
For #N/A, confirm that the lookup value really exists and has the same underlying type as the source value. For #REF!, inspect whether a reference was deleted or whether the returned position exceeds the INDEX range. If an error is being hidden by IFERROR, temporarily remove the wrapper while diagnosing the formula. A clean display is useful only after the underlying lookup has been checked.
Build and troubleshoot the formula step by step
Constructing the lookup in stages makes it easier to locate a problem than writing a nested formula all at once. In Microsoft Excel for Microsoft 365 on Windows, try this process:
- Confirm the lookup value and source list are in the expected columns, and check whether the values are numbers or text.
- Test MATCH on its own with the lookup cell, lookup range, and exact-match argument. A position such as 3 means the third item in the lookup range matched.
- Place that MATCH expression inside INDEX and check that the return range has the corresponding rows in the same order.
- Add IFNA or IFERROR only after the plain lookup works, then choose a fallback that tells the reader what happened.
- Test a known match, a value that is not present, and a blank input if blanks are possible.
Use absolute references for fixed source ranges when copying the formula, and leave the input reference relative if each row has its own lookup value. If Excel rejects a formula copied from a web page, check whether the computer’s regional settings expect semicolons rather than commas as argument separators; the displayed separator can vary by locale. The function names and logic remain the same.
INDEX MATCH compared with XLOOKUP
In Microsoft 365, XLOOKUP can perform many of the same lookups and includes an optional argument for a value to return when no match is found. For a one-column exact lookup, that can make the formula shorter. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but not in older perpetual Excel releases such as Excel 2019.
INDEX MATCH remains valuable for compatibility with older workbooks and for readers who already use the pattern. It can also search in one range and return from another without requiring the return column to be on a particular side of the lookup column. Neither approach fixes inconsistent source data, duplicate keys, or an incorrect lookup value; those still need attention. On Mac and Excel for the web, the formulas work in supported versions, although menus and interface details can differ from Windows.
Frequently Asked Questions
How do I show blank instead of #N/A in INDEX MATCH?
Wrap the lookup in IFNA and use an empty text value as the fallback. This displays an apparently blank result for a missing match. Remember that the cell still contains a formula, so it is not the same as a truly empty cell.
Should I use IFERROR or IFNA with INDEX MATCH?
Use IFNA when the expected problem is simply that no exact match exists and you want other errors to remain visible. Use IFERROR only when you intentionally want to replace any error generated by the lookup, not just #N/A.
Why does INDEX MATCH return #N/A even though the value appears in the list?
Check for a text-versus-number difference, extra spaces, hidden characters, or a slightly different value. Also confirm that MATCH uses 0 for an exact match and that the lookup range includes the row you expect.
Does MATCH return the last duplicate?
No. With the standard exact-match pattern, MATCH returns the position of the first matching item it encounters in the lookup range. If duplicate entries are meaningful, use a method designed to identify the specific occurrence you need.
Can I use IF to catch #N/A?
IF is for testing a condition, not for catching errors by itself. Combine a logical test with the lookup if you need conditional behavior, or use IFNA or IFERROR to provide a fallback when the lookup errors.
Related
- Excel Data Validation List: Can It Show Two Columns?
- Excel Data Validation List With a Blank Option
- Excel Data Validation List Without a Cell Reference?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel Page Breaks: How to Insert, Remove, and Fix ThemAn Excel page break determines where worksheet content moves onto the next printed page. In Microsoft Excel for…
- Excel #DIV/0! Error: How to Prevent It in Your FormulasTo avoid Excel’s #DIV/0! error, make sure a formula never divides by a blank or zero denominator. For…
- Excel NPV Errors: Why Results Look Wrong and How to Fix ThemExcel NPV errors most often come from including the initial investment in the wrong place, using a discount…
- Excel MID Function Not Working? Causes, Errors, and FixesIf Excel’s MID function is not working, check that its arguments are in the right order, the starting…
- Hyperlink Not Working in Word Header? Fixes That HelpA hyperlink in a Word header usually works, but Word may require a modifier-click to open it, or…
- Excel Cell Showing Date Instead of Number? Fix the FormatIf an Excel cell shows a date instead of a number, select it and change its number format…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.