Explanation
In this example, the goal is to demonstrate how an INDEX and (X)MATCH formula can be set up so that the columns returned are variable. This approach illustrates one benefit of the 2-step process used by INDEX and MATCH: Because INDEX expects a numeric index for row and column numbers, it is easy to manipulate these values before they are returned to INDEX.
At the core, this formula is a two-way INDEX and MATCH formula:
=INDEX(C5:G16,XMATCH(I5,B5:B16),XMATCH(J4:L4,C4:G4))
Working from the inside out, the first XMATCH function returns a row number:
XMATCH(I5,B5:B16) // returns 6
Because April 6 is the sixth value in the range B5:B16, the first XMATCH function returns 6. The second XMATCH function is configured like this:
XMATCH(J4:L4,C4:G4) // returns {1,3,5}
This is the clever bit. Notice the lookup_value is the range J4:L4, which contains "Red", "Blue", and "Green", and the lookup_array is the desired columns in C4:G4. Because we are asking XMATCH to find three values, XMATCH returns 3 results in an array like this:
{1,3,5}
The numbers in this array are the numeric positions of the "Red", "Blue", and "Green" in the range C4:G4. After both MATCH formulas run, we have the following inside INDEX:
=INDEX(C5:G16,6,{1,3,5}) // returns {7,9,8}
The INDEX function then returns the values for April 6 (row 6 in the data) for the "Red", "Blue", and "Green" columns only, and the values spill into the range J5:L5.
Note: in a modern version of Excel that supports dynamic array formulas, this formula will just work. In an older version of Excel, you will need to use the MATCH function instead of XMATCH and enter the formula as a multi-cell array formula.
XLOOKUP
How you can solve this problem with XLOOKUP? One approach is to use the XMATCH function together with the CHOOSECOLS function to alter the original data like this:
=XLOOKUP(I5,B5:B16,CHOOSECOLS(C5:G16,XMATCH(J4:L4,C4:G4)))
Here, the lookup value is the date in cell I5 as before, and the lookup array is the range B5:B16. The return_array is created on the fly with XMATCH and CHOOSECOLS. XMATCH returns the array {1,3,5} as explained above, and the result from MATCH is returned to CHOOSECOLS as the col_num1 argument and C5:G16 as the array. CHOOSECOLS then returns columns 1, 3, and 5 to XLOOKUP as the return_array.
