The demo data that I use in my “Create a Dashboard with Excel” training course revolves around product sales for a company that makes and sells ice cream. Here’s a snip of that data:

Recently on a course I was asked “How could you show on one line (for each flavour) the state that ordered the most and the state that ordered the second most?

Here is my solution to the question:

First I created a pivot table to show all the flavours and all the states and the Revenue per flavour/state combination. In the pivot table I turned off the grand totals as they are not necessary in this solution (Design tab > Grand Totals > Off for Rows and Columns)

Columns O and P (not part of the pivot table) contain the formulas to calculate the name of the state that generated the highest and second highest revenue per flavour

For each row (3:10) I need Excel to look across the row between columns B and M for the highest (and then second highest) value in that row. It then has to look UPWARDS to row 2, capture the name of the state and return it as the answer to the formula.

This solution requires the use of several functions combined together: LARGE, INDEX and MATCH

The Large Function

This retrieves the largest, second largest etc value within a given range. For example:

LARGE(B3:M3,1) returns the largest value within the range B3:M3
LARGE(B3:M3,2) returns the second largest value within the range B3:M3

The Index Function

Many people would suggest using the HLOOKUP function (similar to VLOOKUP). It works by looking across a row from left to right, finding what you are looking for (in this case the highest value on that row) and looking at another row to find the final answer.

However, the row containing the final answer must be BELOW the row being searched and in this example the row containing the name of the state (the final answer) comes from a row ABOVE the numbers. So I can’t use HLOOKUP. However a combination of INDEX and MATCH functions will work…

For column O (which state ordered the most), I used the following formula:

=INDEX($B$2:$M$10,1,MATCH(LARGE(B3:M3,1),B3:M3,0))

The INDEX function returns a value from the intersection of a row and column reference

For example, if I put =INDEX(B2:M10,1,5) I am telling Excel to retrieve whatever is in F2. This is because F2 is from the first row and fifth column of the range B2:M10

In our example, the ROW parameter will always be 1 because it will always be looking for a value from row 2 (the first row of the range B2:M10).

However, the COLUMN parameter will be different depending on which column contains the largest value. So to generate the “column parameter” for the INDEX function I used the MATCH function

The Match Function

The MATCH function looks for a given value within a row-based or column-based list and returns the position within that list of the item being looked for.

For example, if the number 100 was in A5, =MATCH(100,A1:A10,0) will return 5 because 100 (the value to be searched for) is the 5th item in the range A1:A10 (the 3rd parameter (0) tells Excel that you want an exact match)

So back to my formula. For the COLUMN part of the Index function, I used the following:

MATCH(LARGE(B3:M3,1),B3:M3,0)

This tells Excel to return the position within B3:M3 of the largest value in B3:M3

So you can see that by combining INDEX and MATCH functions you can overcome the restrictions imposed by HLOOKUP and VLOOKUP

You can download the demo Excel file here