Finding cells that meet multiple criteria is a common task in Excel. In this tutorial, we look at how to use the combination of **INDEX**, **MATCH**, and **LARGE** functions in Excel to find specific data in a dataset.

We first describe and explain how each function works.

Tutorial Content

## Combination of INDEX, MATCH, and LARGE functions

We use the following dataset of product sales to explain how the combination of INDEX, MATCH, and LARGE functions can be used to pull data that meets certain conditions from the top of a dataset:

We recommend that you first convert the data range to a table using the steps below:

- Select the whole dataset and click
**Insert >> Tables >> Table**.

- Ensure that the defaults in the
**Create Table**dialog box are correct and click the**OK**button.

The data range is converted to a table:

We want to find out the top 10 best-performing products from the table. We proceed as follows:

- Type
**Ranking**,**Top 10**, and**Product**in cells**D1**,**E1**, and**F1**respectively. Type values 1 to 10 in range**D2:D11**. Your dataset should look like the one below:

- Select cell
**E2**and type in the following formula:

`=LARGE(Table1[Sales],D2)`

- Press the Enter key and double-click or drag down the
**fill handle**to copy the formula down the column.

We get the sale amounts of the 10 best-performing products.

We need to find out the products associated with the amounts.

- Select cell
**F2**and type in the following formula:

`=INDEX(Table1[Product],MATCH(LARGE(Table1[Sales],D2),Table1[Sales],0))`

- Press the Enter key and double-click or drag down the fill handle to copy the formula down the column.

- Click the down arrow in the
**Sales**header and click**Sort Largest to Smallest**:

The results confirm that the formula returned the correct values:

### Explanation of the formula

`=INDEX(Table1[Product],MATCH(LARGE(Table1[Sales],D2),Table1[Sales],0))`

- Table1[Product]. Look for the product in the Product column.
- MATCH(LARGE(Table1[Sales],D2),Table1[Sales],0. Find the largest value in the Sales column in the position in cell D2 and match it to the product indexed in the Product column.

## INDEX Function

The **INDEX** function returns a value or the reference to a value from within a table or a range.

It has two forms:

- The array form that returns the value of a specified cell or array of cells
- The reference form that returns a reference to specified cells.

### Array form

The array form of the **INDEX** function returns the value of an element in a table or an array selected by the row and column number indexes.

Use this form if the first argument to **INDEX** is an array constant.

Syntax:

**INDEX(array, row-num, [column_num])**

The function has the following arguments:

**array**. It is required. It is an array constant or a range of cells.

**Note:** If the array contains only one column or row, the corresponding **column_num** or **row_num** argument is optional. If the array has more than one row or column, and only the **row_num** or **column_num** argument is used, an array of the entire row or column is returned.

**row_num**. it is required unless the**column_num**argument is present. It selects the row in the array from which to return a value. If**row_num**is omitted, then the**column_num**is required.**column_num**. It is optional. It selects the column in the array from which to return a value.

Note: If both **row_num** and **column_num** arguments are used, the **INDEX** function returns the value in the cell at the intersection of **row_num** and **column_num**. If we set the **column_num** and **row_num** to 0 (zero) the **INDEX** function returns the array of values for the entire row or column respectively.

We enter the **INDEX** function as an array formula if we want to use the values returned as an array. If we are using Excel 365, we press **Enter** otherwise we press **Ctrl + Shift + Enter**.

### Reference form

The reference form of the INDEX function returns the reference of the cell at the intersection of a particular column and row. If the reference is made up of non-adjacent selections, we can pick the selection to look in.

Syntax:

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

The reference form of the **INDEX** function has the following arguments:

**reference**. It is required. It is a reference to one or multiple cell ranges.

**Note:** If you are entering non-adjacent ranges for the reference, enclose the reference in brackets. If each area in the reference contains only one column or row, the **column_num** or **row_num** argument, respectively, is optional. For example, for a single column reference, use INDEX(reference, row_num,,)

**row_num**. It is required. It is the number of the row in reference from which to return a reference.**column_num**. It is optional. It is the number of the column in reference from which to return a reference.**area_num**. It is optional. It selects a range in reference from which to return the intersection of row_num and column_num. The first area entered or selected is numbered 1, the second is 2, and so on. If the area_num is omitted, area 1 is assumed.

## MATCH function

The **MATCH** function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

The function returns the relative position of an item in an array that matches a specified value in a specified order.

Syntax:

**MATCH(lookup_value, lookup_array, [match_type])**

The **MATCH** function has the following arguments:

**lookup_value**. It is required. It is the value that you want to match in the**lookup_array**. It can be text, logical value, number, or a cell reference.**lookup_array**. It is required. It is the range of cells being searched.**match_type**. It is optional. It specifies how Excel matches the lookup_value with the values in the lookup_array. The value can be -1, 0, or 1. The default value is 1.

**Note:** If the **match_type** value is 1 or omitted, the **MATCH** function finds the largest value that is less than or equal to the **lookup_value**. The values in the **lookup_array** must be arranged in ascending order.

If the **match_type** value is 0, the **MATCH** function finds the first value that is exactly equal to the **lookup_value**. The values in the **lookup_array** can be in any order.

If the **match_type** value is -1, the **MATCH** function finds the smallest value that is greater than or equal to the **lookup_value**. The values in the **lookup_array** must be arranged in descending order.

## LARGE function

Returns the k-th largest value in a data set. For example, the third-largest number.

Syntax:

**LARGE(array, k)**

The LARGE function has the following arguments:

**array**. It is required. It is the array or range of data for which you want to determine the k-th largest value.**k**. It is required. It is the position, from the largest, in the array or data range to be returned.

## Conclusion

In this tutorial, we looked at how to use the combination of **INDEX**, **MATCH**, and **LARGE** functions in Excel to find specific data in a dataset.