Formula - Index/Collect/Match by rank
OK, I'm stumped!
Here is my source sheet
And here is my destination sheet
What I want is...
Column: Contractor A
Return Value From: Source Sheet Company Column
Where:
- Source Sheet, Type Column Matches Destination Sheet Category Coulmn
- Source Sheet Rating Coulmn is the highest value.
Columns: Contractor B & Contractor C
Same as above but 2nd and 3rd highest values.
Any help, I've completely confused myself trying to work it out.
Thanks.
Best Answer
-
To incorporate the 1st, 2nd, and 3rd highest rating, you would use @Genevieve P.'s solution and add in
=INDEX(COLLECT({Company Column}, {Category}, Category@row), {Rating}, @cell = LARGE({Rating}, 1))
Just change the 1 to a 2 or 3 as needed.
Answers
-
Instead of INDEX(MATCH, try using INDEX(COLLECT.
This would use the COLLECT function to filter your source sheet based on your criteria (the Category), then you can use the row index feature in the INDEX function to identify if you're looking for the first match, second match, or third.
Try something like this:
Contractor A
=INDEX(COLLECT({Company Column}, {Category}, Category@row), 1)
Contractor B
To find the second match, swap the 1 out at the end for 2, like so:
=INDEX(COLLECT({Company Column}, {Category}, Category@row), 2)
Contractor C
=INDEX(COLLECT({Company Column}, {Category}, Category@row), 3)
Let me know if that makes sense and works for you.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
To incorporate the 1st, 2nd, and 3rd highest rating, you would use @Genevieve P.'s solution and add in
=INDEX(COLLECT({Company Column}, {Category}, Category@row), {Rating}, @cell = LARGE({Rating}, 1))
Just change the 1 to a 2 or 3 as needed.
-
Ah! Thanks @Paul Newcome! I totally missed the Rating.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!