How to Find Matching Values in Two Columns on Another Sheet in Spreadsheets
Question details
The user needs to match data based on two specific criteria located in separate columns on a different worksheet to retrieve a related value.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Looking up and returning a value based on multiple criteria (e.g., two name fields) that exist on a separate worksheet within the same workbook.
- Observed behavior
- A multi-criteria formula is required to accurately match two conditions simultaneously across sheets and return the related value without errors.
Ensure both worksheets are in the same workbook and that your lookup columns do not contain trailing spaces, as this can prevent accurate matching.
Use XLOOKUP with Boolean Logic
The most robust method to match multiple criteria simultaneously without altering your data structure or creating helper columns.
By using boolean logic (multiplying two conditions), XLOOKUP can evaluate multiple criteria arrays simultaneously. When both conditions are met, it returns a 1 (TRUE), which is then matched to retrieve your target value.
Click on the cell in your main sheet where you want the matching value to appear.
Type the formula: =XLOOKUP(1,(A2='2023'!$A$2:$A$9)*(B2='2023'!$B$2:$B$9),'2023'!$F$2:$F$9,""). Be sure to replace the sheet name '2023' and cell ranges with the actual names and ranges in your workbook.
Press Enter to execute the formula. Click the bottom-right corner of the cell and drag the fill handle down to copy the formula to the remaining rows.
Use XLOOKUP with Concatenated Ranges
A simpler approach that joins the text of the two columns together using the ampersand (&) operator to form a single lookup value.
Use the FILTER Function
An alternative dynamic array approach that filters the return column based on the two conditions.
Easily Manage Complex Data Lookups with WPS Spreadsheet
WPS Office provides full support for advanced dynamic array functions like XLOOKUP and FILTER, allowing you to cross-reference complex data across sheets quickly and efficiently.
- 1. Download WPS Office: Download and install WPS Office Free from the official website.
- 2. Open your workbook: Launch WPS Spreadsheet and open the file containing your data sheets.
- 3. Apply advanced functions: Click any cell and use the formula bar to input your multi-criteria XLOOKUP or FILTER functions exactly as you would in Microsoft Excel.
Frequently Asked Questions
Why does my XLOOKUP formula return an error even when the data matches?
This is typically caused by hidden trailing or leading spaces in your text fields, or a mismatch in data types (e.g., text vs. numbers). Try using the TRIM function within your XLOOKUP formula to clean the data strings before matching.
Can I use VLOOKUP instead of XLOOKUP for matching two columns?
VLOOKUP natively supports only a single lookup value. To use VLOOKUP for two columns, you would first need to create an extra 'helper column' in your source data that concatenates the two values, which is why XLOOKUP is a cleaner and more direct solution.
What happens if there are multiple matches for the two columns?
By default, XLOOKUP will return the first match it encounters in the range. If you need to retrieve all instances where the two columns match, you should use the FILTER function, which will spill all matching results into the adjacent cells.
