Function Problems

Compare a Cell with a Range and Return Custom Text in Excel

Phi Hung VoPhi Hung Vo Nov 13, 2026 869 views

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.

Compare a Cell with a Range and Return Custom Text in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

Click the empty cell where you want the custom match result to appear.

2
Enter the exact match formula

Type =IF(ISNUMBER(MATCH(A1,A2:A101,0)),"Some Text","Other Text") and press Enter.

Check for an exact match with MATCH and ISNUMBER
Match found: If A1 exactly equals any cell in A2:A101, the formula returns "Some Text"; otherwise it returns "Other Text".
WPS Spreadsheets Formula

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. 1. Open WPS Office Spreadsheets: Launch WPS Office and open the workbook that contains the range A2:A101.
  2. 2. Select the result cell: Click an empty cell where you want to display the custom match result.
  3. 3. Enter the formula: Type =IF(ISNUMBER(MATCH(A1,A2:A101,0)),"Some Text","Other Text") and press Enter.
  4. 4. Confirm the result: Check that the cell returns the correct custom text when A1 is found in the range.
Uses familiar Excel MATCH and IF functionsSupports both exact and partial text match formulasFull compatibility with .xlsx and .xls filesFree, lightweight spreadsheet editor
QA img-9

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.