INDEX and MATCH in Excel or Google Sheets

INDEX and MATCH are two formulas that are often used together. They help you do a two-way lookup, i.e. find data based on two parameters - one in row and the other in columns.

They are a great alternative to the VLOOKUP and XLOOKUP formulas.

INDEX and MATCH in Excel or Google Sheets

INDEX and MATCH are two formulas that are often used together. They help you do a two-way lookup, i.e. find data based on two parameters - one in row and the other in columns.

They are a great alternative to the VLOOKUP and XLOOKUP formulas.

MATCH

MATCH Formula finds the position of a known value within a series of data. The position of Apple is 4 in the following set ‘Banana, Orange, Grapes, Apple, Peach’.

The syntax of this formula is

EXCEL
=MATCH (lookup_value, lookup_array, [match_type])

GOOGLE SHEETS
=MATCH (search_key, range, [search_type])

lookup_value or search_key: the value whose position we need to find in the array

lookup_array or key_range: the array which contains the lookup_value or search_key

[match_type] or [search_type]: optional value; look for the exact lookup_value (or search_key) or a close match. There are three options:

1 (– Less than): returns the position of the largest value that is less than or equal to the lookup_value. The data must be in ascending order

0 (– Exact match): returns the position of the first occurrence of value same as the lookup_value. The lookup_array can be in any order

-1 (– Greater than): returns the position of the smallest value that is greater than or equal to the lookup_value. The lookup_array must be in descending order

Example 1: A company’s sales data is arranged in descending order of total sales by their sales staff. We can use the Match formula to find out the relative rank of a salesperson.

To find the relative rank of total sales by Jim, the formula =MATCH(E19,D6:D15,0) can be used as shown below:

Data for implementation of the MATCH formula

Since the data is sorted in descending order, Jim’s relative value in the list shows his rank. We use the 0 – Exact match in [match_type] as we are looking for the position for a known value, which is Jim in this case.

Example 2: A company’s sales data is arranged in descending order of total sales by their sales staff. We can use the Match formula to find out how many of the entire sales staff sold more than 6,500 units. To find how many people sold more than 6,500, the formula =MATCH(E18,I6:I15,-1) can be used as shown below:

MATCH formula returns the number of values that are more than 6,500

Since the data is sorted in descending order, we can use -1 – Greater than in the [match_type]. We get the result as 7. If we check the data, then we will see that 7 salespeople have sold more than 6,500 units.

INDEX

INDEX formula is used when in an array or reference we know the row and column number and want to find the corresponding value. Often this formula is used along with other functions.

This formula has two variants, one with arrays and the other one with references; we will look at both.

The syntax of the INDEX array formula is as below

EXCEL
=INDEX (array, row_num, [column_num])

GOOGLE SHEETS
=INDEX (reference, [row], [column])

array or reference: the range of cells, table, or an array

row_num or [row]: the row number in the array where the value is to be returned from. If this is not provided, then column_num or [column] must be provided

[column_num] or [column]: the column number in the array where the value is to be returned from. If this is not provided, then row_num or [row] must be provided

Example: For the sales report given below, if we want to find out the number of Jackets sold by Bailey, then we know that Bailey’s data is in the 4th row and Jackets’ data is in the 3rd column; we can use the formula =INDEX(E6:I15,4,3) as below:

INDEX Formula to extract the total no. of jackets sold by Bailey

This formula will start throwing incorrect results if the data in the table is moved or sorted.

Also, realistically the row numbers and column numbers are not readily known.

In such a case we use the INDEX formula along with the MATCH formula. The formula =INDEX(E6:I15,MATCH(E19,E5:I5,0),MATCH(E20,D6:D15,0)) can be used as shown in the image below

INDEX & MATCH formula together help in performing a 2 way lookup

Understanding the INDEX with reference

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

reference: this is a set of range of cells, table, or array

row_num: the row number in the array where the value is to be returned from. If this is not provided, then column_num must be provided

[column_num]: the column number in the array where the value is to be returned from. If this is not provided, then row_num must be provided

[area_num]: refers to which range, table, or array to refer from the reference

Example: For the price list given for beverages and their flavour, we can use the formula as indicated to find out the price for flavour and size for the type of beverage.

Two sets of ranges are provided in the formula F6:H8 (Coffee) or F9:F11 (Tea). The IF statement in the [area_num] parameter decides which range to look at for returning the price.

The final formula that can be written is =INDEX((F6:H8,F9:H11),MATCH(E17,E6:E8,0),MATCH(F17,F5:H5,0),IF(D17="Coffee",1,2))

Using the reference option with INDEX formula allows you to select the table in which you want to search for the data

All Excel & Google Sheets Resources

INDEX and MATCH in Excel or Google Sheets

INDEX and MATCH in Excel or Google Sheets

MATCH

CONTENT

COMPANY

LEGAL

CONTENT

COMPANY

LEGAL