The INDEX Function

A Two-in-One Function

The INDEX function in Excel is a powerful tool to retrieve a value from a specific location within a range or array. Simply put, the INDEX function returns the value of a cell in a specified row and column of a given range.

I see the INDEX function as a “messenger.” I send it on an errand to get an item, giving it specific directions, and it comes back with the exact item I need. Whenever I want to retrieve a value or values from my data, my go-to function is the INDEX function. I specify the row and column (when necessary), and Excel retrieves the value for me.

Let’s dive into this function

The INDEX function has three arguments and the syntax is written as

INDEX(array, row_num, [column_num])

Argument Breakdown

• array: what is to be retrieved. This argument is compulsory.
• row_num: the position of the row of the value(s) to retrieve. This argument is compulsory.
• column_num: the position of the column of the value(s) to retrieve. This argument is optional.

A Two-in-One Function: Array and Reference Form

Another interesting fact about the INDEX function is that it can be used in two forms: the array form and the reference form. Hence, the INDEX function has two arguments.

The Array Form

In array form, the INDEX function returns a value or an array of values from a specified range based on the given row number and/or column number. In my experience, this form is used more frequently than the reference form.

Get the Drill

This dataset provides information on various stationery items available in a bookshop, including their prices and quantities in stock.

Retrieve the list of stationary

=INDEX($B$5:$B$13,0)

Syntax Breakdown

• $B$5:$B$13: range to retrieve the data.
• 0: instructs Excel to return the entire column within the specified range.

Retrieve the details for pens (Price)

=INDEX($C$5:$D$13,2,0)

Syntax Breakdown

• $C$5:$D$13: range to retrieve the data.
• 2: row number within the specified range.

Retrieve the details for pens (Price and Quantity)

=INDEX($C$5:$D$13,2,0)

Syntax Breakdown

• $C$5:$D$13: range to retrieve the data.
• 2: row number within the specified range.
• 0: column number, instructs Excel to return the entire column within the specified range.

The Reference Form

In reference form, the INDEX function returns a reference to a cell or range of cells. This is useful when pointing to a specific cell or range within your selected range. The reference form can handle multiple ranges, allowing for more flexible and dynamic data referencing.

The syntax for the reference form is written as

INDEX(reference, row_num, [column_num], [area_num])

Arguments Breakdown

• reference: a reference to one or more ranges. If you are referring to multiple ranges, you must enclose them in parentheses.
• row_num: the row number within the specified range(s) from which to return a value. If row_num is set to 0, INDEX returns the entire column.
• column_num (optional): the column number within the specified range(s) from which to return a value. If column_num is set to 0, INDEX returns the entire row.
• area_num (optional): this is the most important part of the argument when referencing multiple ranges. The area_num selects which range to use.

Get the Drill

Retrieve the Price (Highlighter)

=INDEX(($C$6:$C$9, $C$11:$C$15), 1, , 1)

Syntax Breakdown

• ($C$6:$C$9, $C$11:$C$15): specifies two ranges.
• 1:this specifies the row number within the chosen range.
• Blank: this is left empty, which defaults to the first column (in this case, this is the list of stationary).
• 1: specifies which range to use. 1 means the first range ($C$6:$C$9).

Retrieve the Price (Scissors)

=INDEX(($C$6:$C$9, $C$11:$C$15), 5, , 2)

Syntax Breakdown

• ($C$6:$C$9, $C$11:$C$15): specifies two ranges.
• 5:this specifies the row number within the chosen range.
• Blank: this is left empty, which defaults to the first column (in this case, this is the list of stationary).
• 2: specifies which range to use. 2 means the second range (set 2).

Retrieve the list of stationary

=INDEX(($B$6:$B$9, $B$11:$B$15), 0, , 2):

• ($B$6:$B$9, $B$11:$B$15): specifies two ranges
• 0: indicates the entire row.
• Blank: this is left empty, which defaults to the first column (in this case, this is the list of stationary).
• 2: specifies which range to use. 2 means the second range (set 2).

This dataset represents a bookshop inventory list divided into two sets, each containing various stationery items along with their respective prices and quantities available.

In Summary…

The INDEX function in Excel retrieves a value or an array of values from a specific location within a range or array. It can be used in two forms: array form, which returns values based on row and column numbers, and reference form, which returns a reference to a cell or range of cells and can handle multiple ranges. This function is versatile for various data lookup and retrieval tasks.

See you in the upcoming blogs

Cheers!

You can connect with me on Linkedin