How to Match Two Criteria in Excel to Replace Email Addresses
Question details
The user wants to compare two specific criteria (State and Contact Name) across two different Excel sheets and update an email address based on a successful match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Updating an export list by cross-referencing contact names and states from a master sheet to pull the correct email addresses.
- Observed behavior
- The goal is to replace the email in the export sheet only when both the state and contact name exactly match the source sheet, leaving the existing email intact if there is no match.
Ensure both of your Excel sheets are within the same workbook for easier referencing, and verify that your column headers (State, Name, Email) are consistent to avoid array size errors.
Use XLOOKUP with Multiple Criteria and IFNA
Combine XLOOKUP with boolean logic to match two conditions simultaneously, using IFNA to preserve original data when no match is found.
The XLOOKUP function can handle multiple criteria by multiplying conditions together. This boolean logic returns an array of 1s and 0s, where 1 means all conditions are true. Wrapping the formula in IFNA ensures that if the criteria aren't met, the original email address remains unchanged instead of displaying an error.
Determine the column letters for State, Name, and Email in both your source sheet (e.g., Sheet1) and your destination sheet (e.g., Export).
In your Export sheet where the new email should appear, begin the formula: =XLOOKUP(1, (Sheet1!A:A=Export!A2)*(Sheet1!B:B=Export!B2), Sheet1!C:C). Adjust the column letters to match your actual State, Name, and Email columns.
Enclose the XLOOKUP function in IFNA to keep the existing email if no match is found by modifying it to: =IFNA(XLOOKUP(1, (Sheet1!A:A=Export!A2)*(Sheet1!B:B=Export!B2), Sheet1!C:C), Export!C2). Export!C2 represents the cell of the current email.
Press Enter to execute the formula. Then, click the bottom-right corner of the cell and drag the fill handle down to apply this logic to all rows in your export list.
Use INDEX and MATCH for Older Versions
If you are using an older version of Excel that does not support XLOOKUP, use an array formula combining INDEX and MATCH.
Effortlessly Use Advanced Lookup Functions in WPS Spreadsheet
WPS Office fully supports advanced functions like XLOOKUP, IFNA, and array formulas, allowing you to seamlessly match multiple criteria and manage your datasets without missing a beat.
- 1. Open Your Workbook in WPS: Launch WPS Office and open your .xlsx workbook containing the two sheets you need to cross-reference.
- 2. Insert the XLOOKUP Formula: Select the target cell in your Export sheet, navigate to the formula bar, and type your multi-criteria XLOOKUP formula exactly as you would in Excel.
- 3. Calculate and Save: Press Enter to instantly process the data, drag the formula down to apply it to the rest of the column, and save your seamlessly updated document.
Frequently Asked Questions
Why is my multi-criteria XLOOKUP returning a #VALUE! error?
This error typically occurs if the arrays you are comparing do not have the same number of rows. Ensure that you are referencing full columns (e.g., A:A) or strictly identical ranges (e.g., A2:A100 and B2:B100) across all your criteria conditions.
Can I match three or more criteria using this method?
Yes. You can match as many criteria as you need by continuing to multiply conditions in the lookup_array argument. For example, adding a third criterion looks like: (Sheet1!A:A=Export!A2)*(Sheet1!B:B=Export!B2)*(Sheet1!D:D=Export!D2).
What does the '1' mean in the XLOOKUP formula?
When you multiply criteria like (Sheet1!A:A=Export!A2), the spreadsheet creates arrays of TRUE (1) and FALSE (0) values. Multiplying them together returns 1 only when all conditions are TRUE. The lookup_value '1' tells XLOOKUP to find the row where all criteria evaluate to true.
