How to find the largest Items in a list and their corresponding Item names?

Heval A.

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 😊

SoS | Dan Palenchar

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...