Excel INDEX Function

The INDEX function in Excel is used to return the value of a specified cell, an array of cells, or to return a reference to specific cells. The INDEX function takes a range of cells and a position, and returns the value of the cell at the specified position. The INDEX function may also be paired with the MATCH function for a lookup that is more flexible and powerful than a simple VLOOKUP.

Syntax of the INDEX Function

The INDEX function in Excel has four arguments (values in the function separated by commas) and is written as follows:

=INDEX(range, rownumber, [columnnumber], [area_number])

Range (required) The range argument is the range of cells or array constant to be indexed.
Row\Number (required) The row\number is the position of the row in the range or array to be indexed.
Column\number (optional) The column\number is the position of the column in the range or array to be indexed. This would set an intersection for the row\_num.
Area\number (optional, only for referernce form) The area\number is the certain range or array reference that is being indexed. This argument is only used when your formula includes multiple ranges or arrays.

The INDEX function has two forms: the array form and the reference form. The reference form of the INDEX function allows for the use of multiple arrays, whereas the array form only allows for one.

The INDEX function in Array Form

The array form of the INDEX function in Excel only allows for one array (range) to be referenced and is written as follows:

=INDEX(range, rownumber, [columnnumber])

	A	B	C
1	State	Population (Millions)	Electoral Votes
2	California	39.56	55
3	Texas	28.7	38
4	Florida	21.3	29
5	New York	19.54	29
6	Illinois	12.74	20
7	Pennsylvania	12.81	20

For instance, let's say you wanted to know the number of electoral votes for Texas, so you want to return the value located in the second row and the third column of the table above (disregarding the headers). One would use the formula:

=INDEX(A2:C7, 2, 3)

The formula would return the value 38.

If one wanted to list all the values associated with the third row of the above array, one would use the following formula:

=INDEX(A2:C7, 3, 0)

The formula would return the values of: Florida \| 21.3 \| 29

The INDEX Function in Reference Form

The INDEX function in reference form allows for multiple arrays to be referenced within the function.

=INDEX(reference, rownumber, [columnnumber], [area_number])

Like the Array form of the INDEX function, the column\number argument is OPTIONAL. The area\number is only OPTIONAL if one array is referenced. If two or more arrays are referenced, then the area\_number is REQUIRED.

	A	B	C	D	E
1	Array #1			Array #2
2	Drink	Caffeine (mg)		Drink	Sugar (g)
3	Monster	140		Monster	27
4	Rockstar	160		Rockstar	29.4
5	Bang	300		Bang	0

For example, if you wanted to find the sugar content of Bang, you can reference the third row and the second column (disregarding the header row) of the second array, one would use the following function:

=INDEX((A3:B5, D3:E5), 3, 2, 2)

The formula would return a value of 0.

The INDEX MATCH Function Versus the VLOOKUP Function

As mentioned earlier, the INDEX function's most common use is in place of the VLOOKUP function. Both the INDEX and the VLOOKUP functions are highly powerful tools to search one's Excel spreadsheet, but the INDEX function is often better suited when paired with the MATCH function.

Remember, the Array form of the INDEX function REQUIRES a reference argument and a row\number or column\number (if the row\_number is left as 0). That's great if you know exactly which value you're looking for. If you don't know the exact value you're searching for, use the MATCH function.

The syntax of the INDEX MATCH function appears as so:

=INDEX(range, MATCH(lookupvalue, lookuprange, [match_type]))

	A	B	C	D	E	F
1	State	Population (Millions)	Electoral Votes		State	Electoral Votes
2	California	39.56	55		Florida	?
3	Texas	28.7	38
4	Florida	21.3	29
5	New York	19.54	29
6	Illinois	12.74	20
7	Pennsylvania	12.81	20

For example, if one wanted to use the INDEX MATCH function to find the number of electoral votes for the state of Florida, they would use the following function:

=INDEX(C2:C7, MATCH(E2, A2:A7, 0))

The formula would return a value of 29.

VLOOKUP could preform the same task as INDEX MATCH by using the following formula:

=VLOOKUP(E2, A2:C7, 3, FALSE)

The formula would also return a value of 29.

The biggest difference between INDEX MATCH and VLOOKUP is that the VLOOKUP function requires a static column (column 3 in our example) and INDEX MATCH uses an array (C2:C7 in our example). If one were to add a column to the data in the range, the VLOOKUP function would not be updated automatically by Excel and may return information from the wrong column. The INDEX MATCH function, on the other hand, ensures the value comes from the correct column.

Excel caps the VLOOKUP character limit at 255, whereas the INDEX MATCH function is limited only by the capabilities of your computer.

Both the VLOOKUP function and the INDEX match function have pros and cons. The VLOOKUP function is much simpler and shorter and is the best choice for smaller static spreadsheets. The INDEX MATCH function is more complex but ensures your spreadsheet will remain functionally intact after later remodeling. The INDEX MATCH function is the best fit for larger dynamic spreadsheets in Excel.

Learn more on how to use the INDEX MATCH function here.

You can read more about Excel's VLOOKUP function here, or try some Excel exercises with INDEX and MATCH!

Continue to INDEX practice exercises! →

Images Referenced

https://excelexercises.com/logo2.png
https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png

INDEX Function Examples and Practice