If you are new to INDEX and MATCH, see the overview hereHow to use INDEX and MATCH.
Two-way INDEX and MATCH
Essentially, this formula employs thetwo-way INDEX and MATCH approach.
The second XMATCH function is used to find the required columns like this:
This is the clever bit.
Note: in a modern version of Excel that supportsdynamic array formulas, this formula will just work.
XLOOKUP
How can this problem be solved usingXLOOKUP?
Thereturn_arrayis created on the fly with XMATCH and CHOOSECOLS.
CHOOSECOLS then returns columns 1, 3, and 5 to XLOOKUP as thereturn_array.
XLOOKUP on the other hand deals withranges.
To make column ranges dynamic, you sometimes need to use another function like CHOOSECOLS.
you’re able to use INDEX to retrieve individual values, or entire rows and columns.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
The columns to return are provided as numbers in separate arguments.
Each number corresponds to the numeric index of a column in the given array.
