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.
The INDEX function in Excel has four arguments (values in the function separated by commas) and is written as follows:
=INDEX(range, row_number, [column_number], [area_number])
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 array form of the INDEX function in Excel only allows for one array (range) to be referenced and is written as follows:
=INDEX(range, row_number, [column_number])
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 allows for multiple arrays to be referenced within the function.
=INDEX(reference, row_number, [column_number], [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.
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(lookup_value, lookup_range, [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.
You can read more about Excel's VLOOKUP function here, or try some Excel exercises with INDEX and MATCH!