How to lookup the name of the highest selling sales person in the month

In this lesson, we look at a specific example where you have a table of sales data, and you need to find out the name of the person who had the highest sales for the month. It's one of those things that seems like it should be easy until you actually try to do it. Most people will immediately think of using VLOOKUP and somehow combining it with the MAX function, which may work in some cases but not all.

The solutions we present here are not the only way of achieving this, but the do have the advantage of solving the problem with a single formula. The methods here could also be used for a variety of other applications as well.

The challenge - write a formula to find the name of the salesperson with the highest sales

Suppose you have a table of sales data, and you want to find out the person that made the highest sales in the month:

Using VLOOKUP will work sometimes, but not in this example

We can use the MAX function within the VLOOKUP function to find the highest sales figure in the sales column using this formula:

=VLOOKUP(MAX(B2:B5),B2:B5,1,0) (you can learn more about VLOOKUP here and find out about the MAX function here)

The problem with this scenario is that the Salesperson column is to the left of the Sales column, but VLOOKUP can only return values to the right of the lookup column, but the Salesperson column is to the left in this example. So we would either have to swap the columns around, or find another solution.

INDEX/MATCH is one solution

Combining INDEX and MATCH in a single formula is a common (and better) alternative to VLOOKUP. We can include the MAX function as part of our formula to return the result we need:

=INDEX(A2:A5,MATCH(MAX(B2:B5),B2:B5,0)) (you can learn more about using INDEX and MATCH instead of VLOOKUP here).

This formula:

1. Finds the maximum sales figure in column 2.
2. Finds which row it appears in, within the range B2:B5
3. Looks in the range A2:A5, using the row number calculated in step 2, to find the name of the salesperson.

INDIRECT/ADDRESS/MATCH is another solution

We can also solve this problem using the INDIRECT, ADDRESS and MATCH functions. This formula is a bit more complicated, but the use of INDIRECT and ADDRESS opens up the option of dynamically calculating the column from which the name value is to be retrieved.

=INDIRECT(ADDRESS(MATCH(MAX(B1:B5),B1:B5,0),1,4)) (you can learn more about using ADDRESS and INDEX here).

This formula:

1. Uses the MAX function to find the maximum value in the range B2:B5.
2. Uses the MATCH function to find which row in the range B2:B5 contains the maximum value we found in step 1.
3. Adds 1 to the value calculated in step 2, since our MATCH range, B2:B5, starts in row 2 of the spreadsheet.
4. Calculates the address of the cell in column A using the row number calculated in step 3.
5. Uses the INDIRECT function to return the value in the cell address found in step 4.

Dynamically determine which column in which to lookup the name within your formula

The example in this lesson is pretty simple - we have two columns of data, where we need to find a value in one column, and return the value from the same row in the other column. However, most scenarios are likely to be more complicated than this. This raises the question - what if we don't know which column we need to look in to find the result we want? What if we need to calculate which column as part of our formula?

It's possible to combine the techniques introduced in this lesson to achieve a solution where you can do just this. We won't cover it here, but you can find a worked example for this scenario here.

Summary

In this lesson, we looked at how to find the maximum value in one column, and find the related value in the same row in another column.

• We saw that the VLOOKUP and MAX functions would work as long as the column containing the related value is to the right of the column containing the lookup value.
• We looked at the usual alternative to VLOOKUP, INDEX/MATCH, which solved our problem since it can look to in columns to the left of the lookup value as well as columns to the right. For that reason, it's worth considering using INDEX/MATCH as a permanent alternative to VLOOKUP.
• We then used INDEX/ADDRESS as another way to solve the problem. This was more complicated, but provides the potential for greater flexibility.

Finally, the goal of this lesson was to introduce some advanced methods of solving a specific problem in Excel. Hopefully you can use the techniques covered here to extend other aspects of the way you use Excel.