Return X Instead of #N/A in an Excel MATCH Formula
Question details
The user wants a MATCH formula to display X instead of #N/A when a lookup value is not found.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Checking whether a value exists in a column, potentially in another workbook.
- Observed behavior
- MATCH returns a position for a match and #N/A when no match exists.
Identify the cell containing the lookup value and the column to search. If the lookup column is in another workbook, open that workbook and check its file and sheet names.
Use ISNUMBER and MATCH to mark missing values
Return X when an exact match is not found and leave the result blank when one is found.
MATCH returns a numeric position when it finds a value. ISNUMBER converts that result into TRUE or FALSE for the IF formula.
In Excel, click the cell where you want the lookup result to appear.
Type =IF(ISNUMBER(MATCH(A1,B:B,0)),"","X") and press Enter. Replace A1 with the lookup cell and B:B with the column to search.
Change the value in A1 to test the formula: a value found in column B returns a blank, while a missing value returns X.

Mark missing values in another workbook
Use an external reference when the lookup column is in a separate Excel file.
Mark missing matches in WPS Spreadsheet
Use the same IF, ISNUMBER, and MATCH functions in WPS Spreadsheet to display X for values that are not found.
- 1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
- 2. Select a result cell: Click the cell where you want to display the lookup result.
- 3. Enter the lookup formula: Type =IF(ISNUMBER(MATCH(A1,B:B,0)),"","X"), adjust A1 and B:B to match your data, and press Enter.
- 4. Test and save: Check a matching and a missing value, then select Save to keep the formula in your workbook.

Frequently Asked Questions
Why does MATCH return #N/A instead of FALSE?
MATCH returns the position of a found value and #N/A if it cannot find one. Wrap MATCH in ISNUMBER to produce a TRUE or FALSE result for IF.
How do I return X when a value is found instead?
Use =IF(ISNUMBER(MATCH(A1,B:B,0)),"X",""). This returns X for a match and a blank when no match is found.
What does the 0 in MATCH do?
The final argument 0 tells MATCH to look for an exact match. For example, MATCH(A1,B:B,0) searches column B for the value in A1.
Can I search a column in another workbook?
Yes. Open the source workbook and use a reference such as '[Workbook 2.xlsx]Sheet 1'!$A:$A as the lookup range. Replace the file and sheet names with your own.
Why does the formula show X for a value I expect to find?
Check the lookup range and source reference, then compare the values for differences such as extra spaces or numbers stored as text. ISNUMBER can also hide other MATCH errors by treating them as FALSE.




