How to Match Criteria Across Sheets Using XLOOKUP and FILTER in Excel
Question details
The user needs to find a line item that matches a specific department across two different worksheets, while also ensuring the result falls within a defined numeric range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing data across multiple sheets to find matching items based on multiple criteria, such as department names and specific numeric limits.
- Observed behavior
- A need to return the first matching item that fulfills multiple logical conditions in two separate tables without manual filtering.
Ensure your spreadsheet software supports modern dynamic array functions like FILTER, LET, and VSTACK, which are essential for complex multi-condition array operations.
Use the LET and FILTER Functions to Match Items Across Sheets
This method filters both sheets by the required criteria and compares the results using a single, efficient array formula.
By utilizing the LET function, you can define variables for your filtered arrays, making the formula easier to read and process. We will combine FILTER to handle the logical criteria and VSTACK with UNIQUE to find overlapping data.
Select your data range in the first sheet, press Ctrl+T, and name it tbSheetA. Repeat this for the second sheet, naming it tbSheetB. This ensures your formula automatically adapts to new data.
Construct the logical tests to isolate the data. For example, use FILTER(tbSheetA[ITEM], (tbSheetA[ITEM]>=50)*(tbSheetA[ITEM]<=100)*(tbSheetA[DEP]="TargetDept")) to filter the first sheet.
Wrap your filters into a LET formula to process both sheets simultaneously. Assign variables to the Sheet A array and Sheet B array, then compare them.
Use the VSTACK function to stack the filtered arrays, followed by UNIQUE to identify common items. Wrap it in TAKE(filtered_array, 1) or INDEX to output only the first common line item.
Use XLOOKUP with Helper Columns
If dynamic array functions are too complex to manage in one cell, you can use helper columns to concatenate criteria and use XLOOKUP to find matches.
Easily Process Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like XLOOKUP, FILTER, and LET, allowing you to easily match complex criteria across multiple sheets with seamless Microsoft Excel compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing file containing the multiple worksheets.
- 2. Select the target cell: Click on the cell where you want the matched item result to appear.
- 3. Enter the array formula: Type your combined LET and FILTER formula exactly as you would in standard spreadsheet software.
- 4. Calculate the result: Press Enter to instantly process the data and return the cross-sheet match without requiring any additional plugins.
Frequently Asked Questions
Why does my FILTER formula return a #CALC! error?
A #CALC! error typically occurs when the FILTER function finds no matches based on your specified criteria. You can handle this gracefully by adding the optional 'if_empty' argument at the end of your FILTER formula, such as FILTER(array, include, "No matches found").
Can I use VLOOKUP instead of XLOOKUP to match multiple criteria?
VLOOKUP natively supports only a single lookup value. To match multiple criteria with VLOOKUP, you must use helper columns to concatenate those criteria into a single string. XLOOKUP is generally preferred as it can handle multiple criteria arrays directly within the formula.
Are dynamic array formulas compatible across different spreadsheet software?
Yes, modern spreadsheet tools like WPS Spreadsheet and recent versions of Microsoft Excel fully support dynamic array functions. Files utilizing formulas like FILTER, SORT, and UNIQUE will function seamlessly across these compatible platforms.
