You’ve probably come across the **MIN** and **MAX** functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know *who* or *what* got the lowest/highest figure?

Let’s take a simple example;

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the **MIN** and **MAX** functions I can quickly find the lowest and highest values using **MIN(B2:B11)** or **MAX(B2:B11)**, but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where **INDEX** and **MATCH** come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with **INDEX** and **MATCH**.

To get the name of the person with the lowest/highest spend I need the following formula;

**=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)**

The **MATCH** part of the formula find finds the lowest value in cells **B2:B11**. It then uses this number and finds its position within the range. In this example the **MATCH** function will return the value 4 i.e. it is the 4^{th} value in the selected column of data. The index function then looks within range **A2:A11** and looks in the 4^{th} row – the value returned by **MATCH**. You don’t need to reference the column because there is only one column selected in the array portion of the **INDEX** function.

The find the person with the highest spend, the formula would be;

**=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)**

So as you can see **INDEX** and **MATCH**, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.