How to find the largest Items in a list and their corresponding Item names?
Hi,
I'm trying to identify the TOP 5 most Requested Items in my Inventory list. I have a formula in place that finds the value of the Top 5 (Formula #1); however, my formula (Formula #2) that finds the corresponding Item Names of the TOP 5 has some limitations. Here's the breakdown:
CURRENT FORMULA
Formula #1 returns the value of TOP 5 most used Items (where n = 1 to 5):
=LARGE([Approved Requests]3:[Approved Requests]84, n)
Formula #2 returns the Item Name of the TOP 5 most used Item:
=INDEX(Item3:Item84, MATCH(LARGE([Approved Requests]3:[Approved Requests]84, n), [Approved Requests]3:[Approved Requests]84))
LIMITATIONS
When two items have the same value, Formula #2 always returns the Item name of the first occurrence (due to MATCH function searching from top to bottom). Is there a workaround for this?
Thanks 😊
Answers
-
Depending on your use case - if you just want to see what the top 5 most requested are, you could make a report from whatever your source Sheet is and filter it to only include rows that have a value in your first formula of 5 or less.
If it needs to be on another Sheet it would take some creative problem solving I think... perhaps using a multi select column and COLLECT() with a CHAR(10) separator somehow to pull in all values that meet the LARGE() value and show them as a group when there are duplicate values.
Perhaps you could use RANKEQ() instead of of LARGE(), but would need to account for some variability in values as they won't always be integers...
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!