How to Return Yes or a Matching Value from Another Column in Excel
Question details
The user needs to compare data across two worksheets based on three columns. If all three columns match, the formula should return "Yes". If only the first two columns match, it should return the corresponding value from the third column of the second sheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Comparing datasets across two worksheets to identify exact row matches, while providing a specific fallback cell value from another column when only a partial match occurs.
- Observed behavior
- The user is attempting to combine match verification and data retrieval into a single formula, requiring complex logical checks across multiple criteria.
Ensure both sheets have identical data structures in the referenced columns, and verify that your spreadsheet software supports the XLOOKUP function (available in Microsoft 365, Excel 2021, and modern versions of WPS Office).
Use a Combined IF, COUNTIFS, and XLOOKUP Formula
This approach uses COUNTIFS to check for an exact match across all three columns. If not found, it uses an array-based XLOOKUP to retrieve the value from column C based on matching columns A and B.
The COUNTIFS function is excellent for verifying if multiple conditions are met simultaneously. By nesting it inside an IF statement, we can output 'Yes' when an exact match across columns A, B, and C is confirmed.
If the exact match fails, the XLOOKUP function uses Boolean logic (multiplying arrays) to search for rows where only columns A and B match, returning the corresponding value from column C of the target sheet.
Click on the cell in Sheet1 (e.g., cell D2) where you want the result to appear.
Begin by typing the IF and COUNTIFS functions to check if columns A, B, and C match across both sheets: =IF(COUNTIFS(Sheet2!A:A, Sheet1!A2, Sheet2!B:B, Sheet1!B2, Sheet2!C:C, Sheet1!C2)>0, "Yes",
Complete the formula by appending the XLOOKUP function to handle cases where only A and B match: XLOOKUP(1, (Sheet2!A:A=Sheet1!A2)*(Sheet2!B:B=Sheet1!B2), Sheet2!C:C, "No"))
Press Enter to execute the complete formula. Once calculated, drag the fill handle from the bottom-right corner of the cell downwards to apply the formula to the remaining rows.
Seamlessly Compare Complex Data with WPS Spreadsheet
WPS Spreadsheet offers full native support for advanced functions like XLOOKUP, COUNTIFS, and array formulas. You can easily compare multiple columns across different sheets with high performance, completely free.
- 1. Open your workbook in WPS Office: Launch WPS Spreadsheet and open the file containing your two sheets of data.
- 2. Navigate to the Formula tab: Access the Formulas tab to use the Insert Function tool if you need assistance structuring your query.
- 3. Apply the combined formula: Paste the =IF(COUNTIFS(...), "Yes", XLOOKUP(...)) formula directly into your target cell.
- 4. Use the Fill Handle: Double-click or drag the small square at the bottom right of the cell to effortlessly copy the formula across your dataset.
Frequently Asked Questions
Why does my XLOOKUP formula return a #NAME? error?
The #NAME? error typically occurs if you are using an older version of Excel (such as Excel 2016 or 2019) that does not support the XLOOKUP function. You can use an INDEX and MATCH combination instead, or switch to WPS Office which supports XLOOKUP for free.
How do I perform a multi-criteria lookup using INDEX and MATCH?
If you do not have XLOOKUP, you can use =INDEX(Sheet2!C:C, MATCH(1, (Sheet2!A:A=Sheet1!A2)*(Sheet2!B:B=Sheet1!B2), 0)). In older Excel versions, you must press Ctrl+Shift+Enter to execute this as an array formula.
Can I return a blank cell instead of "No" if there are no matches?
Yes. In the XLOOKUP formula, simply replace the "No" at the end with empty quotation marks (""). The final portion of the formula will look like: ..., Sheet2!C:C, ""))
