Formula Errors

Return X Instead of #N/A in an Excel MATCH Formula

Steve KSteve K Nov 18, 2026 868 views

Question details

The user wants a MATCH formula to display X instead of #N/A when a lookup value is not found.

Return X Instead of #N/A in an Excel MATCH Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, click the cell where you want the lookup result to appear.

2
Enter the formula

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.

3
Check the results

Change the value in A1 to test the formula: a value found in column B returns a blank, while a missing value returns X.

Use ISNUMBER and MATCH to mark missing values
Mark matches instead: To return X for a match and a blank for no match, swap the IF results: =IF(ISNUMBER(MATCH(A1,B:B,0)),"X","").
WPS Spreadsheet

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. 1. Open your spreadsheet: Launch WPS Office and open your workbook in WPS Spreadsheet.
  2. 2. Select a result cell: Click the cell where you want to display the lookup result.
  3. 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. 4. Test and save: Check a matching and a missing value, then select Save to keep the formula in your workbook.
Mark missing exact matches without displaying #N/AOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and controls
microsoft office alternative - wps office

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.