Function Problems

How to Compare a Cell with a Range of Values Using Excel Formulas

Kushani Nimanthika Oct 22, 2026 870 views

Question details

The user needs an Excel formula to check if a specific cell value (such as a subscription ID) exists within a given range of values, while also handling blank cells properly.

How to Compare a Cell with a Range of Values Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Comparing a specific identifier against a master list or range of values to verify its presence in a dataset.
Observed behavior: The user requires a dynamic formula that evaluates to TRUE or FALSE depending on whether the target cell matches any value in the specified lookup range.

Before you start

Ensure that the range you are comparing against is locked using absolute references (like $C$532:$CZ$532) if you plan to copy or drag the formula across multiple rows.

Solution 1Recommended

Use ISNUMBER and MATCH to Compare Values

This is the most reliable method to verify if a single cell's value exists anywhere within a specified array or range.

The MATCH function looks for a specific value in a range and returns its relative position. When the value is not found, it returns an #N/A error. By wrapping MATCH in the ISNUMBER function, you can force Excel to return a clean TRUE (if found) or FALSE (if not found).

Select the Target Cell

Click on the empty cell where you want the TRUE/FALSE result to appear.

Enter the MATCH Formula

Type =MATCH([@[Subscription Id]], '4.1_Assumptions'!$C:$C, 0) to find the position of the value. Replace the cell references with your actual target cell and lookup range.

Wrap with ISNUMBER

Modify the formula to =ISNUMBER(MATCH([@[Subscription Id]], '4.1_Assumptions'!$C:$C, 0)). This converts the numerical position into TRUE and errors into FALSE.

Press Enter and Apply

Hit Enter to apply the formula. Drag the fill handle at the bottom-right corner of the cell downwards to apply the formula to the entire column.

Use ISNUMBER and MATCH to Compare Values

Inverting the Result: If you want to flag values that do NOT exist in the lookup range, wrap the entire formula in a NOT function: =NOT(ISNUMBER(MATCH([@[Subscription Id]], '4.1_Assumptions_Base'!$C$532:$CZ$532, 0))).

Solution 2

Handle Blank Cells Using the OR Function

Use this method if you need the formula to return TRUE when the target cell is blank, preventing unnecessary FALSE flags on empty rows.

Powerful Spreadsheet Alternative

Compare Data Ranges Easily with WPS Spreadsheet

WPS Office provides a robust, highly compatible Spreadsheet tool that perfectly supports advanced functions like ISNUMBER, MATCH, and OR. You can effortlessly manage and compare complex datasets.

1. Open WPS Spreadsheet: Launch WPS Office and open your dataset workbook.
2. Select the Result Cell: Click the cell where you want the formula to display the evaluation result.
3. Input the Formula: Enter your =ISNUMBER(MATCH(...)) formula in the formula bar at the top of the screen.
4. Apply to Column: Press Enter and double-click the fill handle at the bottom-right corner of the cell to automatically apply the formula down your dataset.

Fully compatible with Microsoft Excel formulas and .xlsx files.Supports advanced data analysis functions like MATCH, VLOOKUP, and XLOOKUP.Free to use with a lightweight installation package.Intuitive interface familiar to Microsoft Office users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MATCH formula return an #N/A error?

The MATCH function returns #N/A when it cannot find the exact lookup value in the specified range. Wrapping your MATCH formula in the ISNUMBER function converts this error into a clean FALSE result.

Can I compare a cell against a horizontal range (a row)?

Yes, the MATCH function works with both vertical columns and horizontal rows. Simply change your lookup array to a row reference, such as $C$532:$CZ$532, and ensure you use absolute references if copying the formula.

How do I check if a value does NOT exist in a range?

You can use the NOT function around your existing formula. For example, =NOT(ISNUMBER(MATCH(A2, B:B, 0))) will return TRUE if the value in cell A2 is entirely missing from column B.

Does WPS Spreadsheet support the MATCH and ISNUMBER functions?

Yes, WPS Spreadsheet is fully compatible with these functions and evaluates them exactly the same way Microsoft Excel does, ensuring seamless data analysis without modifying your existing formulas.

Excel & Data Problems