Formula - Index/Collect/Match by rank

BullandKhmer

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.

Paul Newcome

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.

Genevieve P.

Hi @BullandKhmer

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

Paul Newcome

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.

Genevieve P.

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