Fix Incorrect Results from an Excel IF ISNUMBER MATCH Formula
Question details
The user needs to correct an IF(ISNUMBER(MATCH())) formula that returns incorrect TRUE results when comparing values between worksheets.

- 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.
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.
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.
Click the cell containing the IF(ISNUMBER(MATCH())) formula, such as the result cell next to A2.
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).
Press Enter, then fill or copy the corrected formula down the column if you need to check additional values.

Remove hidden spaces and inconsistent text values
Use this solution when values look identical on screen but still produce unexpected match results or non-matches.
Make number and text formats consistent before matching
Use this method when IDs, codes, or numbers may be stored as text in one worksheet and as numeric values in another.
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. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the lookup formula.
- 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. Set MATCH to exact mode: Add 0 as the third MATCH argument, such as =ISNUMBER(MATCH(A2,Sheet2!A:A,0)).
- 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. Fill the corrected formula: Drag the fill handle down from the corrected formula cell to apply the exact-match check to the remaining rows.

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.




