We can use the combination of **INDEX**, **MATCH**, and **SMALL** functions to find the data that is in the nth position of a dataset.

Tutorial Content

## Application of the combination of INDEX, MATCH, and SMALL functions

We use the following dataset of product sales in our illustration. We use the combination of the INDEX, MATCH, and SMALL functions to find the 10 least-performing products.

We first need to convert the data range to a table. We use the following steps:

- Select the data range and click
**Insert >> Tables >> Table**.

**Or**

Select the data range and press **Ctrl + T**.

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

The data range is converted to a table.

## The 10 least-performing products

We find the 10 least-performing products by using the following steps:

- Type
**Rank**,**Bottom 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:

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

- Press Enter and drag the fill handle to cell
**E11**to copy down the formula.

We get the sale amounts of the 10 least-performing products in the data set.

## Get the associated products

To get the products associated with the sale amounts we do the following:

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

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

- Press Enter and drag the fill handle to cell
**F11**to copy down the formula.

- Check that the formula has returned the correct values. Click the
**Sort/Filter**button in the**Sales**header and click**Sort Smallest to Largest**.

The results confirm that the formula returned the correct results:

**Explanation of the formula**

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

- Table1[Product]. Look for the product in the Product column.
- MATCH(SMALL(Table1[Sales],D2),Table1[Sales],0. Find the smallest value in the Sales column in the rank in cell
**D2**and match it to the product indexed in the Product column.

## Conclusion

In this tutorial, we used the combination of the Excel functions of **INDEX**, **MATCH**, and **SMALL** to find the 10 least-performing products in a dataset. This was to illustrate how to get the nth match with INDEX, MATCH, and SMALL functions.