# Creating Top 10 Lists and League Tables in Excel 2007

*Robbie mostly writes about Skyrim but also occasionally sheds light on the oddities of Microsoft applications such as Excel and Outlook.*

## Using the MIN and MAX, SMALL and LARGE, INDEX and MATCH Functions in Excel 2007

Today, I am going to look at the **MIN** and **MAX** functions alongside the **SMALL** and **LARGE** functions and how they can be used on your data to provide you with the largest and smallest numbers in your data. Using these functions alongside **INDEX** and **MATCH **allows you can create top 10 lists and league tables.

In addition, I will look at the **INDEX** and **MATCH** functions and how they can extract a label to go along with the data gathered using **MIN, MAX, SMALL** and **LARGE** to create a dynamic table with the label (in my case Hub name) and value (in this instance, total number of hits).

**MIN**and**MAX**provide you with the smallest and the biggest number in your data range.**SMALL**and**LARGE**perform a similar function; however, they allow an argument which can for example allow you to determine the 3 largest numbers in your data, or the second smallest.

## Working With the MIN and MAX Functions

I have a number of articles I have created over time and would like to know the minimum and maximum values of my Hub Score, my Hub Views over different periods of time etc. I can easily use **MIN** and **MAX** to display these.

**MIN** and **MAX** are quite straightforward to use, simply start as with all formulas or functions with an = followed by MAX and the range of cells you want to check.

=MAX(<Range>)

**MIN** works in exactly the same way

=MIN(<Range>)

By creating a formula for each column for both the **MIN** and **MAX** function, I end up with the following.

## Working with the SMALL and LARGE functions

**SMALL** and **LARGE** are very useful if you want to create a league table, or to find a specific value, for example the fourth-smallest value in your data range.

For my example above, I want to know the 10 highest values for total overall hits for my published articles. To do this I use the **LARGE** function in ten formulas, starting with 1, 2, 3 and ending with 10 as below.

=LARGE(<RANGE>,1)

And ending with

=LARGE(<RANGE>,10)

This gives me a list as shown below

Similarly, working with **SMALL**, could give me a list of the ten articles with the lowest totals. If I wanted to know the third smallest value for total article views of all my articles, I would use a formula as below, with the range being the column containing the total views.

=SMALL(<RANGE>,3)

## Limitations of MIN, MAX, SMALL and LARGE Functions

If you look at my league table of the ten articles with the highest number of overall hits above once more, you will notice the limitations that these functions have. While they are fantastic at returning the smallest or largest values in a range, or telling you the third smallest value in a range, that is all they tell you.

For my league table it would be very helpful to know which articles they were as well as their totals.

Next, we are going to explore a way to get Excel 2007 to provide us with the names of my top performing articles automatically to go along with their total hits. To do this we are going to use three functions, one of which we have already used **INDEX, MATCH** and**LARGE**

## Using the INDEX and MATCH Functions Alongside LARGE

To create a league table that not only contains the total number of hits for my ten most popular articles but also the names of those articles, we have to use two new functions alongside** LARGE**. These are the** INDEX** and **MATCH** functions.

What we are doing in essence is

- Firstly we ask Excel to find the largest value in the column for total hits using
**LARGE** - We then ask Excel to use
**MATCH**to find the position of that cell in the same column **INDEX**then looks for the cell that is adjacent to that cell in the column that contains the articles names

Voila, it will construct our league table

OK now onto the formula itself shown in the figure below.

OK, so again starting with **LARGE**, it simply looks for the number one largest value in the column that contains the total article views exactly as we did in our earlier example.

For **MATCH**, the syntax is as follows:

=MATCH(lookup_value,lookup_array,match_type)

- The lookup_value was provided by the result given to us by
**LARGE** - The lookup_array is once again the column that contains the total article views and
- The match type is 0 which tells
**MATCH**to look for the number that is exactly equal to the result given to us by**LARGE**

For **INDEX**, the syntax is as follows:

=INDEX(**array**,row_num,column_num)

- The array is the column containing the article names
- The row_num and column_num is provided by
**MATCH**

So, our two formulas together,

=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),1),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,1) provide us with the following table

This is exactly what we wanted to achieve which is brilliant. To create each row, we just increased the value in the **LARGE** part of the formula from 1 to 2 and so on

=INDEX('hubs(1)'!A3:A47,MATCH(LARGE(('hubs(1)'!F3:F47),2),'hubs(1)'!F3:F47)) and =LARGE('hubs(1)'!F3:F47,2)

## Alternatives to INDEX, MATCH and LARGE

Before investigating the possibility of using **INDEX, MATCH** and **LARGE** in combination, I investigated the possibility of using **VLOOKUP.**

**VLOOKUP** is an immensely powerful function, however, it is unable to search a column to the left of the column you are interested in, which is the case in this example. Rather than move the data around each time I extract it into this spreadsheet, I discarded **VLOOKUP** in this instance.

I have another article which looks into **VLOOKUP** in greater detail. I used it to auto-fill prices for various music media and also to auto-fill a customer’s address based on their customer number. My article for **VLOOKUP** can be found here.

A pivot table would have worked perfectly in this instance, but not with the **MIN** and **MAX, LARGE** and **SMALL** functions I had already started to use. In fact, I already have such a table but I wanted to follow through with these functions.

Pivot tables are also very powerful, allowing you to create a table from your data and then change it at will. If you decide that you want your pivot table to provide you the hubs with the lowest Hub Score and then change your mind to the hubs with the top ten hits for the last seven days, this can be done easily and quickly. What’s more, pivot tables are dynamic, when the data changes they can be instantly refreshed to reflect that.

## Conclusion

I hope that you have enjoyed this hub on the **MIN, MAX, LARGE, SMALL, INDEX** and **MATCH** functions. We explored **MIN, MAX, LARGE** and **SMALL** in detail before exploring how **INDEX** and **MATCH** could make those initial functions even more useful.

We started with the first four functions returning a number (for example the largest number of total hits) and using **INDEX** and **MATCH** we were able to return the actual name of that hub to create a dynamic table league table.

As is often the case in Excel 2007 and why it is such an interesting application to learn and also why it is so powerful is that you can take some seemingly unrelated functions and build something using them that is both powerful and very useful. Many thanks for reading and if you have any comments, please feel free to leave them below.

*This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.*

**© 2013 Robbie C Wilson**

## Comments

**Robbie C Wilson (author)** on January 22, 2013:

Thanks for your kind comment Paul. I am glad that you liked the article. Good to hear that the screenshots were helpful and the examples useful. I love looking into the functions available in Excel and figuring out new and interesting ways of using them and then sharing that via HubPages.

**Paul Maplesden** from Asheville, NC on January 22, 2013:

As a keen Excel user, I think this is a really good article. I like the way that you separated out the various functions, providing good examples with each. The use of screenshots was also very useful in summarising the key steps people need to take. A great tutorial for some often overlooked functions, great work.