Explanation
This is a more advanced formula. For basics, see How to use INDEX and MATCH .
Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. Without concatenating values in a helper column , or in the formula itself, there’s no way to supply more than one criteria.
This formula works around this limitation by using boolean logic to create an array of ones and zeros to represent rows matching all 3 criteria, then using MATCH to match the first 1 found. The temporary array of ones and zeros is generated with this snippet:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Here we compare the item in H5 against all items, the size in H6 against all sizes, and the color in H7 against all colors. The initial result is three arrays of TRUE/FALSE results like this:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
Tip: use F9 to see these results . Just select an expression in the formula bar, and press F9.
The math operation (multiplication) transforms the TRUE FALSE values to 1s and 0s:
{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}
After multiplication, we have a single array like this:
{0;0;1;0;0;0;0}
which is fed into the MATCH function as the lookup array, with a lookup value of 1:
MATCH(1,{0;0;1;0;0;0;0})
At this point, the formula is a standard INDEX MATCH formula. The MATCH function returns 3 to INDEX:
=INDEX(E5:E11,3)
and INDEX returns a final result of $17.00.
Array visualization
The arrays explained above can be difficult to visualize. The image below shows the basic idea. Columns B, C, and D correspond to the data in the example. Column F is created by multiplying the three columns together. It is the array handed off to MATCH.
Non-array version
It is possible to add another INDEX to this formula, avoiding the need to enter as an array formula with control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
The INDEX function can handle arrays natively, so the second INDEX is added only to “catch” the array created with the boolean logic operation and return the same array again to MATCH. To do this, INDEX is configured with zero rows and one column. The zero row trick causes INDEX to return column 1 from the array (which is already one column anyway).
Why would you want the non-array version? Sometimes, people forget to enter an array formula with control + shift + enter, and the formula returns an incorrect result. So, a non-array formula is more “bulletproof”. However, the tradeoff is a more complex formula.
Note: In Excel 365 , it is not necessary to enter array formulas in a special way.
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. If you are new to INDEX and MATCH, see the overview here How to use INDEX and MATCH .
Two-way INDEX and MATCH
Essentially, this formula employs the two-way INDEX and MATCH approach . The INDEX function is provided with the data to return, and the MATCH function is used twice: once to get the correct row number, and once to get the correct column number(s). The generic syntax looks like this:
=INDEX(data,XMATCH(),XMATCH())
The first MATCH retrieves a row number, and the second MATCH retrieves the column number.
Note: we are using XMATCH in this example because the configuration is slightly easier (XMATCH defaults to exact match), but the original MATCH function will work fine as well.
In the worksheet shown, the specific formula used in cell J5 is as follows:
=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 used to find the required columns 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. Since we are asking XMATCH to find 3 values, it returns an array with 3 results:
{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 can this problem be solved using 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 .
XLOOKUP vs INDEX and MATCH
This problem illustrates a key difference between XLOOKUP and INDEX and MATCH: because INDEX and MATCH formulas use a numeric index for both rows and columns, it is easy to modify these values before they are used in the INDEX function. XLOOKUP on the other hand deals with ranges . To make column ranges dynamic, you sometimes need to use another function like CHOOSECOLS. Both XLOOKUP and INDEX and MATCH offer flexibility and functionality for manipulating and retrieving data, but your choice between them will depend on the specific needs of your project.
For an in-depth comparison, see XLOOKUP vs INDEX and MATCH
