INDEX is a powerful and versatile function.
you’re able to use INDEX to retrieve individual values or entire rows and columns.
INDEX is frequently used together with theMATCH function.
In the example shown above, the goal is to get the diameter of the planet Jupiter.
Typically, the MATCH function would be used inside INDEX to provide these numbers.
For a detailed explanation with many examples, seeHow to use INDEX and MATCH.
When the range is one-dimensional, you only need to supply a row number.
When the range is two-dimensional, you must supply both the row and column numbers.
Typically, the MATCH function is used to find positions for INDEX.
The formula in G7 is:
MATCH provides the row number (4) to INDEX.
The column number is still hardcoded as 3.
The MATCH function returns the column number (4) and the row number is hardcoded as 2.
Reference as result
It’s important to note that the INDEX function returns areferenceas a result.
you’ve got the option to use theCELL functiontoreport the referencereturned by INDEX.
Two forms
The INDEX function has two forms:arrayandreference.
Most formulas use the array form of INDEX, but both forms are discussed below.
The difference is that thereferenceargument contains more than one range, andarea_numselects which range should be used.
Thearea_numis argument is supplied as a number that acts like a numeric index.
The first array inside reference is 1, the second array is 2, and so on.
MATCH supports approximate and exact matching, andwildcards(* ?)
LOOKUP’s default behavior makes it useful for solving certain problems in Excel.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
This makes FILTER a flexible way to isolate and inspect data without altering the…
