# Formula - Index/Collect/Match by rank

Options
✭✭✭✭✭

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.

• ✭✭✭✭✭✭
Options

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.

• Employee
Options

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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.

• Employee
Options

Ah! Thanks @Paul Newcome! I totally missed the Rating.