How to Populate Excel Data Matching Multiple Criteria (Names and Year Groups)
Question details
The user needs to pull data from an export sheet into a historical overview sheet by matching three specific criteria: first name, surname, and year group. They also require a way to identify unmatched records.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Merging data between two separate worksheets where a single unique identifier is absent, requiring the simultaneous matching of multiple text and numeric fields.
- Observed behavior
- The user struggles to adapt a standard single-criterion XLOOKUP formula to handle multiple matching conditions and correctly flag records that do not exist in the export data.
Ensure that the First Name, Surname, and Year Group columns in both the Export sheet and your Overview sheet have identical data types and contain no trailing spaces.
Use a Multi-Criteria XLOOKUP Formula
Combine multiple lookup values and lookup arrays using the ampersand (&) operator inside an XLOOKUP function to accurately pull data based on First Name, Surname, and Year Group.
XLOOKUP is highly versatile and can process arrays on the fly. By concatenating the lookup values, you create a unique compound key that guarantees a precise match across multiple columns.
Click on the first empty cell in the column of your Overview sheet where you want the Export data to appear (e.g., cell J2).
Enter the formula: =XLOOKUP(A2&B2&C2, 'Export'!A:A&'Export'!B:B&'Export'!C:C, 'Export'!D:D, ""). Adjust A2, B2, and C2 to point to the First Name, Surname, and Year Group on your current sheet.
Press Enter to execute the formula. Click on the bottom-right corner of the cell and drag the fill handle down to apply the formula to all corresponding rows in your dataset.
Flag Unmatched Rows Using COUNTIFS
Use the COUNTIFS function combined with an IF statement to check if the specific combination of Name and Year Group exists, returning 'Yes' if found and 'No' if unmatched.
Easily Manage Complex Formulas with WPS Office
WPS Spreadsheet fully supports advanced functions like XLOOKUP and COUNTIFS out of the box. It makes populating matching data across sheets completely seamless without performance drops, making it an excellent tool for complex data manipulation.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the Export and Overview sheets.
- 2. Access the Formula tool: Click on the 'Formulas' tab on the ribbon and select 'Insert Function' to search for XLOOKUP.
- 3. Input parameters via dialog: Use the intuitive function argument dialog box to select your criteria cells and arrays, avoiding the hassle of manual typing.
- 4. Execute and auto-fill: Press 'OK' and double-click the cell's fill handle to populate the remaining data in your column instantly.
Frequently Asked Questions
Why is my multi-criteria XLOOKUP returning a #VALUE! error?
A #VALUE! error usually occurs if the concatenated arrays in your formula are not of the exact same size. Make sure you refer to whole columns (e.g., A:A&B:B&C:C) or identical finite ranges (e.g., A2:A100&B2:B100&C2:C100).
How do I return multiple columns at once using XLOOKUP?
XLOOKUP can return an array dynamically. If you set your 'return_array' to encompass multiple adjacent columns (for instance, 'Export'!D:E), the single formula will 'spill' the results into adjacent destination columns automatically.
Can I use INDEX and MATCH instead of XLOOKUP for multiple criteria?
Yes. If you prefer INDEX and MATCH, use an array formula format: =INDEX(Return_Range, MATCH(1, (Criteria1=Range1)*(Criteria2=Range2)*(Criteria3=Range3), 0)). Note that depending on your software version, you may need to press Ctrl+Shift+Enter to execute it properly.
