Explanation
In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX and MATCH formula that looks up information using the movie title. If you are new to INDEX and MATCH formulas, this article provides a detailed overview with many examples.
MATCH function
This formula uses the MATCH function to get the row position of Toy Story in the table, and the INDEX function to retrieve a value at that row. MATCH is configured to look for the movie title in cell H4 in the range B5:B16:
MATCH(H4,B5:B16,0)
Note that the match_type argument is set to zero (0), to force MATCH to perform an exact match . MATCH locates “Toy Story” on row 4 and returns this number to INDEX as the row number.
INDEX function
The INDEX function is configured with an array that includes all data in the table, and the column number is hard-coded as 2 to retrieve the Year value from column 2 in the table.
=INDEX(B5:E16,MATCH(H4,B5:B16,0),2) // get year
Once MATCH returns 4 to INDEX as the row number, we can simplify the formula to:
=INDEX(B5:E16,4,2) // returns 1995
INDEX then retrieves the value at the intersection of the 4th row and 2nd column in the array, which is 1995. The other formulas in the example are identical except for the column number:
=INDEX(B5:E16,MATCH(H4,B5:B16,0),2) // year
=INDEX(B5:E16,MATCH(H4,B5:B16,0),3) // rank
=INDEX(B5:E16,MATCH(H4,B5:B16,0),4) // sales
Note: normally, we would use absolute references to lock references like this:
=INDEX($B$5:$E$16,MATCH($H$4,$B$5:$B$16,0),2) // year
This will allow the formula to be copied into the range H6:H8 with these ranges locked. Then only the column number needs to be changed. However, in the examples above, we are using relative references to make the formulas easier to read.
INDEX with a single column
In the example above, INDEX receives an array that contains all data in the table. However, you can easily rewrite the formulas to provide INDEX with one column only, which eliminates the need to supply a column number:
=INDEX(C5:C16,MATCH(H4,B5:B16,0)) // year
=INDEX(D5:D16,MATCH(H4,B5:B16,0)) // rank
=INDEX(E5:E16,MATCH(H4,B5:B16,0)) // sales
In each case, INDEX receives a one-column array that corresponds to the data being retrieved, and MATCH supplies the row number.
INDEX with TRANSPOSE
The current version of Excel supports dynamic array formulas . This means a formula can return multiple values, and these values will spill onto the worksheet into multiple cells. This means it is possible to return the year, rank, and sales value all in one go. The trick is to give INDEX all 3 columns in the range C5:E16, then specify zero (0) for the row number:
INDEX(C5:E16,MATCH(H4,B5:B16,0),0)
Providing zero for the column_num argument will cause INDEX to return an entire row of data . With “Toy Story” in cell H4, we get back a horizontal array of values like this:
{1995,4,394436586} // {year,rank,sales}
This is the year, rank, and sales for Toy Story. To transform the horizontal data into a vertical array, we can nest the INDEX and MATCH formula inside the TRANSPOSE function like this:
=TRANSPOSE(INDEX(C5:E16,MATCH(H4,B5:B16,0),0))
The result is a vertical array that will land in cell H6 and spill into the range H6:H8 all in one step, with only one formula required.
How to use INDEX and MATCH - a detailed introduction with more examples
Explanation
Working from the inside out, the logical criteria used in this formula is this expression:
--(names=G4)
where names is the named range C4:E7. This generates a TRUE / FALSE result for every value in the data, and the double negative coerces the TRUE and FALSE values to 1 and 0 to yield an array like this:
{0,0,0;1,0,0;0,0,0;0,0,0}
This array is 4 rows by 3 columns, matching the structure of “names”. A second array is created with this expression:
TRANSPOSE(COLUMN(names)^0))
The COLUMN function is used to create a numeric array with 3 columns and 1 row, and TRANSPOSE converts this array to 1 column and 3 rows. Raising the result to the power of zero simply converts all numbers in the array to 1. The MMULT function is then used to perform matrix multiplication:
MMULT({0,0,0;1,0,0;0,0,0;0,0,0},{1;1;1})
and the result goes into the MATCH function as the array argument, with 1 as the lookup value:
MATCH(1,{0;1;0;0},0)
The MATCH function returns the position of the first match, which corresponds to the first matching row meeting supplied criteria. This is fed into INDEX as the row number, with the named range “groups” as the array:
=INDEX(groups,2)
Finally, INDEX returns “Bear”, the group Adam belongs to.
Literal “contains” criteria
To check for specific text values instead of an exact match, you can use the ISNUMBER and SEARCH functions together. For example, to match cells that contain “apple” you can use:
=ISNUMBER(SEARCH("apple",data))
This formula is explained here .
