Confused on Index Collect, Join

i have a sheet with a column with the number of records matching a code number (CodeFM) from another sheet. I have summarized the number of records for each code number (along with the code description in a separate sheet from the large master sheet containing the data). All is well here.

At the bottom of the this second sheet, I want to list the top three highest record counts. I have done this using the LARGE(range, rank) function. This works. I want to put the corresponding code number in the cell to the right. If the record counts were unique, I could use the index match function. However, as seen in my example, there are multiple rows in the third highest position; I need to return the CodeFM value for each of the two records that have three instances.

My sheet looks as follows:

Is this possible? I have played with multiple functions including Index, Collect, and Join based on examples in this community to no avail. Any help would be appreciated.

Thanks in advance.

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!