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

Options

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

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

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

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!