How to Find Matching Values Across Multiple Columns in Excel
Question details
The user needs an Excel formula to check if values from one group of columns match values in another group of columns within the same row, while ignoring specific text placeholders.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing two distinct sets of columns (e.g., Directors and Writers) to identify if there is any overlapping name, without triggering false matches from blank placeholders.
- Observed behavior
- The formula needs to output a 'Y' or 'N' boolean result without causing a dynamic-array #SPILL! error or evaluating '(n/a)' strings as true matches.
Ensure your data is formatted as an Excel Table (e.g., Table4) or note the exact cell ranges for both column groups. Verify the exact spelling of the placeholder text you want to exclude.
Use XMATCH with SUM and ISNUMBER
Use this combined formula to safely cross-reference two sets of columns and aggregate the result to prevent #SPILL! errors.
This method utilizes XMATCH to check for the presence of elements from the first group inside the second group. By multiplying the boolean result by a condition that excludes the '(n/a)' placeholder, it perfectly filters out false positives.
Click on the cell in the final column of your row where you want the 'Y' or 'N' result to appear.
Type the following formula: =IF(SUM(--(ISNUMBER(XMATCH(Table4[@[Dir1]:[Dir3]],Table4[@[Wri1]:[Wri4]],0)))*(Table4[@[Dir1]:[Dir3]]<>"(n/a)"))>0,"Y","N"). Adjust the table and column names to match your actual dataset.
Press Enter. The formula evaluates the arrays internally; the SUM function aggregates the results into a single number greater than zero if a valid match exists, triggering the IF statement to return 'Y'.
Use XLOOKUP as an Alternative
You can use XLOOKUP to return matching values, though extra care must be taken to exclude placeholders.
Solve Complex Data Matching Easily with WPS Office
WPS Spreadsheet fully supports advanced dynamic array formulas like XMATCH and XLOOKUP, allowing you to cross-reference complex datasets seamlessly without calculation errors.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your tables.
- 2. Input the array formula: Click the target cell and type =IF(SUM(--(ISNUMBER(XMATCH(. Then, drag to select your first range (e.g., Directors).
- 3. Define the lookup array and exclusions: Select your second range (e.g., Writers) for the lookup array, and add the exclusion criteria <>"(n/a)" to filter out placeholders.
- 4. Execute the formula: Close the parentheses and press Enter. WPS Spreadsheet will calculate the array logic instantly and return 'Y' or 'N' without spill errors.
Frequently Asked Questions
Why does my array formula return a #SPILL! error?
A #SPILL! error occurs when a dynamic array formula attempts to output multiple values, but there is no space for the data to expand, or it is constrained to a single cell. Wrapping your formula in an aggregator function like SUM or MAX reduces the array into a single result, preventing the spill.
Can I use XMATCH and XLOOKUP in older versions of Excel?
No, XMATCH and XLOOKUP are only available in Excel 2021, Microsoft 365, and modern versions of WPS Office. For older versions, you must use a combination of MATCH, ISNUMBER, and an IF array formula, entered by pressing Ctrl+Shift+Enter.
How do I change the formula to ignore blank cells instead of '(n/a)'?
To ignore blank cells, simply replace the condition <>"(n/a)" in the formula with <>"". This will exclude empty cells from generating false positive matches across your columns.
