Index Match Match with rank check

Hello people.

I need to match value on one set of variables within that range I need to return a values that is ranked according to another variable.

Hopefully the workbook explains it better than me.

I believe that index match match can solve this issue, but I haven't been able to figure it out.

Thanks!

Try:

=IFERROR(INDEX($B$4:$B$21,MATCH(1/(1/LARGE(IF($D$4:$D$21=G$3,$C$4:$C$21,0),ROW(A1))),$C$4:$C$21,0)),"")

as an array formula in G4 (confirm with ctrl+shift+enter) and copy down/across.

Thanks!

Yes, that formula seems to work. There's one part I don't understand though and that is the row function - why does that start at A1 and why isn't it locked?

the row function returns the row of the reference. So, ROW(A1) returns 1. This is used as the second argument of the large function, so it tells the formula to return the largest value.

When you drag it down a row, it becomes ROW(A2), which returns 2 and therefore returns the second largest value, and so on.