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:
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:
Finds which row it appears in, within the range B2:B5
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.
Uses the MAX function to find the maximum value in the range B2:B5.
Uses the MATCH function to find which row in the range B2:B5 contains the maximum value we found in step 1.
Adds 1 to the value calculated in step 2, since our MATCH range, B2:B5, starts in row 2 of the spreadsheet.
Calculates the address of the cell in column A using the row number calculated in step 3.
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?
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.
We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.