We can use the combination of INDEX, MATCH, and SMALL functions to find the data that is in the nth position of a dataset.
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:
|
1 |
=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:
|
1 |
=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
|
1 |
=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.