If you are new to INDEX and MATCH formulas,this articleprovides a detailed overview with many examples.
MATCH locates “Toy Story” on row 4 and returns this number to INDEX as the row number.
Then only the column number needs to be changed.
However, in the examples above, we are usingrelative referencesto make the formulas easier to read.
INDEX with TRANSPOSE
The current version of Excel supportsdynamic array formulas.
This means a formula can return multiple values, and these values willspillonto the worksheet into multiple cells.
This means it is possible to return the year, rank, and sales value all in one go.
you might use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
