INDEX Function in Excel Explained & Examples

It is quite surprising to know that our index finger has no muscles and is mainly controlled by the muscles in the palm and forearm. The index finger is also commonly referred to as a pointer finger as we literally use it to pinpoint important facts in a book or simply point the direction to a place.

The INDEX function in Excel works similarly, it pinpoints the value we are searching in a given range of cells. In this article, we will discuss the basics of Excel’s INDEX function and its 2 formula formats. Also, how to use INDEX function in Excel to lookup data points and illustrate a powerful combination lookup tool; INDEX MATCH function in Excel.

How does the INDEX Function Work in Excel?

How does the INDEX Function Work in Excel? The INDEX function in Excel is under the Lookup & Reference Formulas and returns the value from an array or range of cells based on a given position such as row or column number. The INDEX function’s main purpose is to pinpoint the value in a cell using the given position of the cell’s row and column in the range of cells.

2 Formats of the INDEX Formula in Excel

Unlike other common Lookup and Reference formulas, Excel INDEX function has 2 formats:

  1. Array format. Returns the value based on the intersection of the given column and row from a single range of cells.
  2. Reference format. Returns the value based on the intersection of the given column and row from multiple ranges of cells.

Tip: The key to understanding the 2 formats is to differentiate the range of cells to search from- single or multiple ranges of cells.

Array Format of the INDEX Formula In Excel

Where:

Array Formula
– This is a required argument that represents the array or range of cells to get the value from. This argument answers the question,” Which range of cells contains the value you want to get?”

Row_num
– Also, a required argument that represents the row number (0, 1, 2, 3, etc.) of the Array argument. This argument answers the question,” Which row from the range of cells contains the value you want to get?”
– Think of your Excel spreadsheet as a GPS, the Row_num acts as longitude which specifies east or west position.
– If this argument is blank or set to zero, the default is all rows in the array.

[Col_num]
– This is an optional argument which represents the column number (0, 1, 2, 3, etc.)  of the Array argument. This argument answers the question,” Which column from the range of cells contains the value you want to get?”
– If the Excel spreadsheet is a GPS tool, the Row_num acts as latitude which specifies north or south position.
– If Row_num is not provided, the column number is required.

Reference Format of the INDEX Formula In Excel

Where:

Reference
– This is a required argument that represents the multiple ranges of cells to use as a reference.
– This argument is similar to the Array argument but with more than one range of cells and can be adjacent or non-adjacent.
– Think of the Reference argument as a map, this is the given total search area to lookup the value we want.

Row_num
– Similar to the Array Format, this is a required argument that represents the row number (0, 1, 2, 3, etc.) from the cell range.

 [Col_num]
– Just like in the Array Format, this is an optional argument which represents the column number (0, 1, 2, 3, etc.) from the cell range. If Row_num is not given, Col_num is required.

[Area_num]
– This is an optional argument which picks the area (0, 1, 2, 3, etc.) from the Reference argument. Take note, the areas are numbered by the order they are given. This is helpful for non-adjacent cell ranges.
– If this is omitted, the INDEX Function in Excel uses area 1 as default.

To illustrate how to use the INDEX Function in Excel, please feel free to check out this video:

Examples on How to Use INDEX Function in Excel

To illustrate how the INDEX function in Excel works, assume below 5-year table of expenses for the 6 departments of a multinational firm.

The Excel INDEX function can be used to extract department name, year, and dollar expenses from a single and multiple ranges of cells.

INDEX Function in Excel: Return Value from Range of Cells

This is the most common use of the INDEX Formula In Excel, the value in a cell can be extracted from a range of cells or table by specifying the row and/or column number. How to use INDEX function in Excel to get values within this cell range?

Assume we want to get the first or base year presented in the table.

INDEX Formula In Excel =INDEX(A1:F7,1,2) results in year 2000. If you simply look at the table, you can easily pinpoint to double-check this is correct.

To understand how to use INDEX function in Excel below is the process:

  1. Identify the Array argument. Which range of cells contains the value you want to pinpoint? Or which range of cells will Excel search to get the value you want? The Array argument in the cell range A1:F7.
  2. Specify the Row_num argument. Which row from cell range (A1:F7) contains the value you want to pinpoint? Instruct Excel which direction to search, right, or left. The Row_num argument 1 instructs Excel to look at the 1st row.
  3. Specify the Col_num argument. Which column from cell range (A1:F7) contains the value you want to pinpoint? Instruct Excel to look up or down. The Col_num argument 2 instructs Excel to look at 2nd Column.
  4. INDEX function in Excel looks up the value at the intersection of Row 1 (1st row) and Column 2 (2nd column), this results in the base Year 2000.

Note: Depending on how your data is presented, the row and column numbers can be relative and not exactly the row and column numbers in the Excel spreadsheet.

Below are more examples of how to use INDEX function in Excel to search for other values in the table.

INDEX Function in Excel: Return Value from a Column

Instead of using the entire 5-year table, a single column (assume the base Year 2000) can also be used.

Recall the Col_num argument is optional, omit this in the INDEX Formula In Excel since Array argument is a single column only.

INDEX Function in Excel: Return Value from a Row

The INDEX Formula In Excel is also useful to get values from a row. Assume you want to get the values from the 2nd row of the table.

Keep in mind the Col_num argument is optional and since there is no Column to search from, omit this from the INDEX Formula In Excel.

INDEX Function in Excel: Return Value from Multiple Ranges

Assume expanding the 5-year table to include a 2-year forecasted expense; 2005F and 2006F. Unlike previous examples, we will be working with multiple ranges of cells and so will be using the Reference format of the INDEX Formula In Excel.

How to use INDEX function in Excel to get values within the multiple, non-adjacent cell range?

Assume a comparison of the base year (2000) and the forecasted final year (2006F) under all 6 departments is needed. Using the INDEX function in Excel we can get the values needed.

Using the INDEX Formula In Excel =INDEX((B2:B7,H2:H7),1,,2) results in $322. This is the 2006F expense for the Accounting & Finance department. By simply looking at the table, you can easily pinpoint to double-check this is correct.

Let’s dissect this to understand how to use INDEX function in Excel:

  1. Identify the Reference argument. Which multiple ranges of cells contain the value you want to pinpoint? The Reference argument has 2 range of cells- B2:B7 for the base year 2000 and H2:H7 for forecasted year 2006F.
  2. Specify the Row_num argument. Which row from the multiple ranges of cells, B2:B7 and H2:H7, contains the value you want to pinpoint? The Row_num argument 1 instructs Excel to look at the 1st row.
  3. Specify the Col_num argument. You can omit this as this is an optional argument. Also, take note of the 2 reference data is based on single-column separately.
  4. Specify the Area_num argument. Recall this is an optional argument but since the data has more than one range of cell and non-adjacent, this argument must be specified.  The Area argument instructs Excel to search in the 2nd area; H2:H7.
  5. The INDEX function in Excel gets the value from the 1st row of the 2nd area of the Reference argument and results in $322.

As mentioned before, depending on how your data is presented, the row and column numbers can be relative and not literally the row and column numbers.

Below are other examples of how to use INDEX function in Excel with multiple ranges.

INDEX Function in Excel with Other Excel Functions

The Excel INDEX function can be used with other Excel functions such as AVERAGE, SUM, and other functions in Excel.

For example, the INDEX function in Excel and the AVERAGE function can be combined to calculate the average expense for the year 2001.

The INDEX Formula In Excel and AVERAGE formula =AVERAGE(INDEX(B2:F7,,2)) calculates the average expense in the 2nd column of range B2:F7 and results in $280.

To combine the INDEX function in Excel and SUM function, simply use the same format and replace the “AVERAGE” with “SUM”.

The INDEX Formula In Excel and SUM formula =SUM(INDEX(B2:F7,,2)) calculates the total expense in the 2nd column of range B2:F7 and results in $1,678.

The Excel INDEX function can also be used with other lookup functions but is commonly paired with the MATCH function.

Some would argue the MATCH and INDEX function in Excel is much better than VLOOKUP function because:

  1. The MATCH AND INDEX function in Excel can lookup right to left and can work with data arranged vertically and horizontally.
  2. Uses a dynamic column reference. You can insert or remove columns easily without distorting the results.
  3. Unlike the VLOOKUP function where the total length cannot exceed 255 characters, the INDEX MATCH function in Excel has no array restriction.

Despite these reasons, the VLOOKUP function is much widely used as more users gravitate towards it mainly due to its ease of use. Bear in mind VLOOKUP function is only 1 function whereas the MATCH and INDEX function in Excel is 2 separate functions.

Below is a graphic presentation of MATCH and INDEX function in Excel:

To put it simply, the MATCH function formula finds the lookup value’s position (row or column) in the form of a number (1, 2, 3, etc. ) and then hands it over to the INDEX function to find the lookup value.

To illustrate how to use the MATCH and INDEX function in Excel and, assume you want to know the dollar expense of the Accounting & Finance department for a given year.

Using the MATCH and INDEX function in Excel =INDEX(B2:F2,MATCH(I3,B1:F1,0)) the dollar expense under Accounting & Finance department for the year 2002 is $217.

If you want to get the value for 2004, simply input the year under cell I3, and the dollar of expense is $265.

Let’s breakdown the MATCH and INDEX function in Excel.

MATCH formula in Excel: Find the position in a list

  • Cell I3 is an input cell and represents the lookup value, in this case, the given Year.
  • Range B1:F1 represents the lookup array or range of cells to search from.
  • Zero (0) defines the match type, in this case, 0 means an exact match.
  • The result of the MATCH function in Excel is always presented as a number indicating the position. For this example, the result is 3 or 3rd from the list.

INDEX Formula In Excel: Get the value from the known position.

  • The Array argument, B2:F2, is the range of cells where Excel will search the value, the dollar expense.
  • Recall that INDEX Formula In Excel needs to know the position (which row or column) to search from and that’s why the Row_num argument is required.
  • The MATCH formula essentially becomes the Row_num argument and gives the exact position. In this example, the result is 3 or 3rd from the list.
  • From the known position, 3 or 3rd from the list, the INDEX function in Excel results in the value of $217.

Important Elements to Remember with Excel INDEX Function

  • INDEX function in Excel has two formats:
    • The most basic format is the Array Format which lookup a value from a single range of cells, and
    • The Reference Format which lookup a value from multiple ranges of cells.
  • The lookup value in the INDEX formula in Excel can be a numerical value, text string, or reference cell. A text string lookup value is not case sensitive and wildcard characters can be used.
  • The Excel INDEX function returns the following errors:
    • #VALUE! error occurs if any of the given Row_num, [Col_num], or [Area_num] arguments are non-numeric.
    • #REF! error occurs if the given Row_num, [Col_num], or [Area_num] arguments is greater than the number of rows, columns, and area given.
  • The INDEX MATCH function in Excel is a powerful lookup and reference tool, it is more dynamic and versatile than the much popular VLOOKUP function.

Was this helpful?