Top 10 Values with One Excel Formula

Find the top 10 values with one Excel formula. This video shows you how to apply a formula to 10 cells in one go to return the top 10 values and other data associated with them.

Think you need to improve your Excel formula skills?

This video tutorial uses an array function with LARGE to return the top 10 values. And INDEX and MATCH formula is then used to fetch related data.

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[Computergaga]

This answers a common question in the comments thread for this video.

[rickcampbell]

Question: how would the formula change, if searching for the top 10 values in multiple columns, instead of one column?

[selvanayakin]

Suppose more than one same values occured means. How can we find

[shujiexu]

This is really helpful ! Thanks a lot :) But I am facing an issue that I have extract the same value for all top 10 somehow. I do have duplicated values in the "List" column, but do you think that is the case?

[sarathchandran]

Hi Sir, I'm still confused how you entered sales in the formula =Large(sales. Where does this sales came from. Can u just help me out?

[Samivo]

What if two company names have the same value, how are you able to list them both ?

[jemmeyson]

Of all the resources available on this subject I found yours to be most clear and helpful. God Bless!

[TheMasterw]

It's so satisfying finding an Excel tutorial that lets me do exactly what I wanted to do. Thank you!

[astabba]

Love it, however, what is happening is we have duplicated values in column b? Yes, it will take the first one... Anyway, I like it

[aaronroberts]

The problem with the second part where it is indexed is if you have the same values in column 2, will it give 2 different lookup values in indexing column 1. It would be better if it step 1 could take multiple cells as one line.

[matias]

What if you have 2 items with the same numerical value?

[MichaelAlbuquerque]

It does not give me the correct list if 2 values are the same. The first name is then repeated. (Eg. If 2 sales values are 984, then both will return the first name rather than both the names in subsequent cells)... So what modification needs to be done in the formula to get the total list, even if the values are the same.

[KaraokeEman]

Thank you So Much, this is very helpful for me

[StuartFadden]

This is really helpful, so thanks. The only issue I've identified is where the value of 2 items in the sales column are equal, when this occurs the CompanyName index returns the first company name it finds twice. Any ideas of how to fix this?

[baus]

thank you sir. what if the data has same value? it kept refer to the first name with the same value on the table.

[tarankainth]

hey bro if sometimes the value is same of two names then it shows only the one name as same in second.... then what to do next in mobile

[delaney]

Great video! I had a question - I have duplicate values in the "total" range. When I pull the names over, it duplicates a single name rather than filling in all of the names that match that total. (IE: I have 3 $100's, rather than seeing three names, I see one name, three times.)

Is there a way to fix this?

[woodman]

When you have duplicate values it chooses the first name it comes to and list the name twice. How do I get the formula to give me both names that are tied to the duplicate values.

[lordef]

what if I have a third column with "orange" "apple" "banana" and i want to list the top sales only about "orange"? how it could be managed in your opinion?

[TempleOfFive]

This is great, how could i do this across multiple sheets? Say I had similar data as you, but had 40 sheets each named after a year. How would I pull the top 10 from all sheets combined?