How to Return the Second Matching Column with XLOOKUP or INDEX MATCH in Excel
Question details
The user needs a formula to retrieve a value corresponding to the second occurrence of a repeated column header for a specific parameter.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Looking up data across worksheets where column headers are repeated, and retrieving the specific value for the second parameter.
- Observed behavior
- Standard lookup formulas only return the first occurrence of the matching header, ignoring the subsequent occurrences required for the second parameter.
Ensure your spreadsheet software supports the XLOOKUP function. If you are using an older version without XLOOKUP, you will need to use the INDEX and MATCH solution instead.
Use a Nested XLOOKUP Formula
Implement a nested XLOOKUP function to dynamically fetch the correct array intersection, even when column headers repeat.
By nesting one XLOOKUP inside another, you can dynamically define both the row array and the column array. This technique allows Excel to pinpoint the exact cell where the second parameter's criteria meet, avoiding the limitation of stopping at the first match.
Click on the cell where you want the result to appear (for example, cell J2).
Type `=XLOOKUP($I2, $E$2:$G$2, XLOOKUP(J$1, $A$3:$A$4, $E$3:$G$4))` into the formula bar. Ensure you adjust the absolute and relative cell references based on your actual data layout.
Press Enter to calculate the result, then drag the fill handle at the bottom-right corner of the cell across the desired columns and down the rows.
Use INDEX and MATCH for Older Excel Versions
Use the classic INDEX and MATCH combination if XLOOKUP is not supported by your current spreadsheet software.
Effortlessly Manage Complex Data Lookups with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like XLOOKUP, INDEX, and MATCH, making it incredibly easy to retrieve specific data from complex layouts seamlessly.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your duplicated column headers.
- 2. Insert the XLOOKUP function: Select your target cell, go to the Formula tab, and click Insert Function to easily build your nested XLOOKUP formula.
- 3. Calculate and evaluate: Press Enter to retrieve the exact lookup value and confidently drag the formula across your grid.
Frequently Asked Questions
Why does VLOOKUP only return the first matching value?
VLOOKUP searches from top to bottom (or left to right in the case of HLOOKUP) and stops at the very first exact match it encounters. To return a second occurrence or perform a two-dimensional lookup, you must use more advanced formulas like nested XLOOKUP, INDEX MATCH, or FILTER.
Does WPS Spreadsheet support the XLOOKUP function?
Yes, the latest versions of WPS Office fully support the XLOOKUP function. You can use it exactly as you would in Microsoft Excel without needing to adjust the syntax.
How do I handle #N/A errors when my formula doesn't find a match?
If using XLOOKUP, you can use the built-in [if_not_found] argument, such as `=XLOOKUP(A1, B:B, C:C, "Not Found")`. If you are using INDEX and MATCH, wrap your formula in an IFERROR function, like `=IFERROR(INDEX(...), "Not Found")`.
