Excel Magic Trick 967: Formula To Extract Top 5 Numbers & Names, Including Ties or Duplicates



Download Excel File: http://people.highline.edu/mgirvin/ExcelIsFun.htm Concepts: 1. This video is great to learn how to use a formula to extract top 5 numbers (revenues, sales, units or sports scores) and the associated names (Sales Representatives, Employees or Athlete Names) including the situation when there are ties or duplicates in the numbers. 2. What to do when VLOOKUP can't do a lookup for duplicate values or ties and it is not possible to do a concatenated helper column; meaning there are not two lookup values, but instead just one lookup value (number value from LARGE). In this Video: 1. SMALL Function Example: for Excel 2007 or earlier array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, SMALL and ROW. Also see: array operations, comparative operators, join symbol ampersand, and expandable ranges. 2. AGGREGATE Function Example: Excel 2010 or 2013 array formula with the functions: LARGE, COUNTIF, IF, ROWS, INDEX, AGGREGATE and ROW. Also see: array operations, comparative operators, join symbol ampersand, syntax for show nothing or empty cell or null text string and expandable ranges. 3. How Come VLOOKUP will not work 4. Find Hurdle Value for Top 5 using LARGE function 5. Count how many values should be extracted using COUNTIF function, comparative operator and Ampersand (join symbol)

Comments

  1. PLEASE can someone send me the file, the link doesnt work anymore :( i will pay $10. please reach out to me
  2. Hiya. This video is awesome as it is the answer to what I desperately need. But, I need to use it in google sheets and cant quite get it to work. Can anyone assist with this? Many thanks in advance.
  3. Excellent video. So clear to follow and downloadable files to go along with the video... What could be better? I should've found this sooner.

    Thank you so much!
  4. hi, i did it step by step followed all your instructions, but when all the values is changed to zero, then zahid appears to all cells in the manager column. How do we remove that and leave a blank cell when there are no number inputs on the visits column?
  5. Hey! What is that Fuzzy Lookup??
  6. Hi sir in the case of the tie can we add criteria also for the ranking?Thanks
  7. Name not changes auto matic after change
  8. After 2 days of struggling found solution.... thanks a ton bro!!!
  9. Grate formula... Tq
  10. Will this not work if your 'Visits/Manager' list is a pivot table? I'm trying but I keep getting a #Value resulting from the IF( portion equaling my E6 spot. Dammit Excel you frustrate me!
  11. Thank you for sharing this information! Worked perfectly!!!
  12. Great thanks ! And great formula !
  13. Way beyond my feeble Excel skills.
  14. Wow. That is amazing. Thanks Mike for this EXCELlent video.
  15. Thank you <3
  16. So this works great with a single column of data.. but what if your data contained 10 columns, including the 1st column of names?
  17. I'm needing some help. I'm using Excel 2016 to create a volleyball round robin spreadsheet and I'm needing to get the Top 5 players in order, everything seems fine, but the VLookup function isn't returning the name of the players. I don't know what I'm doing wrong...please and thank you!
  18. How come im having #Value show up in my names column which would be his manger column???
  19. You are awesome
  20. Mate really appreciated, you help me a lot buddy. Thank you very much.


Additional Information:

Visibility: 126797

Duration: 17m 34s

Rating: 569