How to Return a Blank When a Value Exists in Another Excel Range
Question details
The user needs to compare two ranges and return a blank cell if a value from the first range is found in the second range, otherwise keeping the original value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two lists of data and filtering out matching values by replacing them with blanks.
- Observed behavior
- A dynamic array formula is required to automatically spill results across multiple cells, showing blanks for matched values and the original data for non-matches.
Ensure that the output range where the formula will spill its results (e.g., C1:C4) is completely empty to prevent a #SPILL! error.
Use IF, ISNUMBER, and XMATCH with Dynamic Arrays
This is the most efficient method for newer spreadsheet versions. It uses a dynamic array to compare ranges automatically and spill the results down the column.
The XMATCH function searches for a specified item in an array and returns its relative position. By wrapping it in ISNUMBER, we can determine if a match was successfully found. Finally, the IF function evaluates this true/false result to output either a blank string or the original value.
Click on the first cell where you want the filtered data to appear (for example, C1).
Type the formula =IF(ISNUMBER(XMATCH(A1:A4,D1:D4)),"",A1:A4) into the formula bar.
Press the Enter key. The formula will automatically spill down through the required cells (e.g., C1:C4), leaving a blank for any matches and retaining the original value for non-matches.
Use IF and MATCH for Older Excel Versions
If you are using an older version of Excel that does not support the XMATCH function or dynamic arrays, you can use the traditional MATCH function and drag the formula down manually.
Effortlessly Compare Data Ranges in WPS Spreadsheet
WPS Office fully supports advanced functions like XMATCH and dynamic arrays, allowing you to quickly compare datasets, clean up data, and apply complex logical formulas with ease.
- 1. Open your Data File: Launch WPS Spreadsheet and open the document containing the two data ranges you want to compare.
- 2. Select Output Cell: Click on the top cell of your intended output column (e.g., C1).
- 3. Enter the Formula: Type =IF(ISNUMBER(XMATCH(A1:A4,D1:D4)),"",A1:A4) and press Enter.
- 4. View the Results: Watch as WPS Spreadsheet instantly calculates and spills the matched blanks and non-matched values down the column.
Frequently Asked Questions
Why am I getting a #SPILL! error when I enter the dynamic array formula?
A #SPILL! error occurs when the dynamic array formula doesn't have enough empty space below it to output all the results. Check the cells directly below your formula (e.g., C2 to C4) and clear any existing text or data to fix the issue.
Can I use COUNTIF instead of XMATCH to return a blank for matching values?
Yes, you can use the formula =IF(COUNTIF($D$1:$D$4, A1)>0, "", A1) and drag it down. However, combining XMATCH with dynamic arrays is generally faster and more efficient for modern spreadsheet tasks since you don't need to manually copy the formula.
Does the XMATCH formula work in all versions of Excel?
No, the XMATCH function and dynamic arrays were introduced in newer versions like Microsoft 365, Excel 2021, and recent WPS Office updates. If you are using Excel 2019 or earlier, you should use the IF and MATCH combination instead.
