Compare a Cell with a Range and Return Custom Text in Excel
Question details
Create an Excel formula that checks whether a cell value matches any value in a specified range and returns one text when a match is found and another when it is not.

- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user is building a worksheet where cell A1 must be compared against the range A2:A101, then a custom text result is returned based on match or no match.
- Observed behavior
- The formula should return "Some Text" for a match and "Other Text" for no match. A variation should match A1 when it appears inside longer text strings in the range.
Open your workbook in WPS Office Spreadsheets and confirm the comparison range, such as A2:A101. Decide whether the formula must match the whole cell exactly or only contain the lookup value.
Check for an exact match with MATCH and ISNUMBER
Use this when A1 should equal the entire value in one of the cells A2:A101 exactly.
MATCH returns the position of A1 in the range when a match exists and #N/A when it does not. ISNUMBER converts that result into TRUE or FALSE so IF can return your chosen custom text.
Click the empty cell where you want the custom match result to appear.
Type =IF(ISNUMBER(MATCH(A1,A2:A101,0)),"Some Text","Other Text") and press Enter.

Check whether A1 appears inside longer text strings
Use this when any cell in A2:A101 may contain A1 together with other text and you still want the result to be treated as a match.
Compare a cell against a range in WPS Office Spreadsheets
Use the MATCH, ISNUMBER, and IF combination in WPS Office Spreadsheets to compare a cell with a range and return custom text for match or no-match cases. WPS Office keeps Excel formulas compatible and opens .xlsx files without extra conversion.
- 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the range A2:A101.
- 2. Select the result cell: Click an empty cell where you want to display the custom match result.
- 3. Enter the formula: Type =IF(ISNUMBER(MATCH(A1,A2:A101,0)),"Some Text","Other Text") and press Enter.
- 4. Confirm the result: Check that the cell returns the correct custom text when A1 is found in the range.

Frequently Asked Questions
Does this formula work in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports the MATCH, ISNUMBER, and IF functions, so the formula works the same way as in Excel.
What does the 0 at the end of the MATCH function mean?
The 0 tells MATCH to find an exact match rather than an approximate match.
How can I return a blank cell instead of "Other Text"?
Replace "Other Text" with two double quotation marks, for example: =IF(ISNUMBER(MATCH(A1,A2:A101,0)),"Some Text","").
Can I compare A1 with a range on another worksheet?
Yes. Use a reference such as Sheet2!A2:A101 in the formula, for example: =IF(ISNUMBER(MATCH(A1,Sheet2!A2:A101,0)),"Some Text","Other Text").
How can I make the comparison case-sensitive?
A standard MATCH is case-insensitive. For a case-sensitive exact match, use an array formula with EXACT and IF instead of MATCH.




