Formula Errors

Fix Incorrect Results from an Excel IF ISNUMBER MATCH Formula

Jason MillerJason Miller Nov 12, 2026 869 views

Question details

The user needs to correct an IF(ISNUMBER(MATCH())) formula that returns incorrect TRUE results when comparing values between worksheets.

Fix Incorrect Results from an Excel IF ISNUMBER MATCH Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
Checking whether a value in one worksheet exists in a dataset on another worksheet.
Observed behavior
The formula returns TRUE for values that should not be treated as matches.
Before you start

Identify the cell being searched and confirm the intended lookup column on the other worksheet. Check whether the values should match exactly, including spaces, punctuation, and number formatting.

Solution 1Recommended

Use an exact-match argument in the MATCH formula

Use this method when the formula returns unexpected TRUE results, especially when the lookup column is not sorted.

MATCH uses approximate matching when its third argument is omitted or set to 1. Add 0 to require an exact match between the lookup value and the source range.

1
Select the formula cell

Click the cell containing the IF(ISNUMBER(MATCH())) formula, such as the result cell next to A2.

2
Edit the MATCH function

In the formula bar or by pressing F2, set the third MATCH argument to 0. For example, use =IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),TRUE,FALSE).

3
Confirm the formula

Press Enter, then fill or copy the corrected formula down the column if you need to check additional values.

Use an exact-match argument in the MATCH formula
Exact matching enabled: MATCH(A2,Sheet2!A:A,0) returns a position only when the value in A2 exactly matches a value in Sheet2 column A.
Work with formulas in WPS Spreadsheets

Check exact worksheet matches with WPS Spreadsheets

WPS Spreadsheets supports IF, ISNUMBER, and MATCH formulas, so you can require exact matches across worksheets and quickly inspect inconsistent data using helper formulas and number-format tools.

  1. 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the lookup formula.
  2. 2. Select the formula result cell: Click the cell with the IF(ISNUMBER(MATCH())) result and use the formula bar to review the worksheet and range references.
  3. 3. Set MATCH to exact mode: Add 0 as the third MATCH argument, such as =ISNUMBER(MATCH(A2,Sheet2!A:A,0)).
  4. 4. Clean inconsistent lookup values: Enter helper formulas such as =TRIM(CLEAN(A2)) or =VALUE(A2) in an unused column when spaces or text-number differences are present.
  5. 5. Fill the corrected formula: Drag the fill handle down from the corrected formula cell to apply the exact-match check to the remaining rows.
Use MATCH with 0 to perform exact cross-sheet lookups.Apply TRIM, CLEAN, and VALUE formulas to normalize lookup data.Open and edit Microsoft Excel .xlsx and .xls workbooks with formula compatibility.Use a familiar spreadsheet interface for formulas, formatting, and worksheet references.
microsoft office alternative - wps office

Frequently Asked Questions

Why does MATCH return an incorrect result when I omit the third argument?

When omitted, MATCH defaults to approximate matching behavior. Use 0 as the third argument, as in MATCH(A2,Sheet2!A:A,0), to search for an exact value.

Can IF(ISNUMBER(MATCH())) check values on another worksheet?

Yes. Reference the other worksheet in the lookup range, for example =IF(ISNUMBER(MATCH(A2,Sheet2!A:A,0)),TRUE,FALSE).

Why do identical-looking values fail an exact MATCH?

The cells may contain leading or trailing spaces, nonprinting characters, or different data types. Test with LEN, then use TRIM, CLEAN, or VALUE to standardize the values.

Does MATCH distinguish between text numbers and real numbers?

Yes, a value stored as text can behave differently from a numeric value. Convert both datasets to the same type through Home > Number Format or with a VALUE helper formula.