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.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

You would use somethign like this:

=JOIN(COLLECT(CodeFM1:CodeFM12, NumberFM1:NumberFM12, @cell = NumberFM@row), ", ")

All comments

Paul Newcome

You would use somethign like this:

=JOIN(COLLECT(CodeFM1:CodeFM12, NumberFM1:NumberFM12, @cell = NumberFM@row), ", ")

Rich G

I got very close to that exact formula but messed up on the "@cell=" part. Your solution worked perfectly. Thank you for this solution

Paul Newcome

Happy to help. 👍️

Rich G

I just noticed that your solution is only returning one value for the third highest cell. It returns FM6-2, but also should be returning FM1-2.

Rich G

Fixed! Mistake in cell range. Thanks again.

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.