Figure out offset to correct row

[MetLife]

Hi,

I used a LARGE function to find the largest value in column "X" based on some conditions. It happens that the column "X" is a column of losses, but column "B" is the policy number for that loss.

So the LARGE function finds the largest loss but I also need to find the corresponding policy number.

2nd largest loss = LARGE(($X$14:$X$175000) * ($D$14:$D$175000=1)*($AD$14:$AD$175000=TRUE),2)

Is there a way to use MATCH() to get the offset, for column "B"?

 

[Fluff]

Are you trying to get the largest, or 2nd largest value from col X?

 

[MetLife]

The largest varies, so I was setting it to "$CU12" because I copied the LARGE function down and CU13=2, CU14=3,... etc...

= LARGE(($X$14:$X$175000) * ($D$14:$D$175000=1)*($AD$14:$AD$175000=TRUE),$CU12)

So I need an offset for each nth LARGEST value

 

[Fluff]

Ok how about

=TAKE(SORT(CHOOSECOLS(FILTER($B$14:$X$175000,($D$14:$D$175000=1)*($AD$14:$AD$175000=TRUE)),-1,1),1,-1),5)

This will return the 5 largest values from col X along with the value in col B. Change the 5 at the end to however many rows you want.

 

[MetLife]

Ok how about

=TAKE(SORT(CHOOSECOLS(FILTER($B$14:$X$175000,($D$14:$D$175000=1)*($AD$14:$AD$175000=TRUE)),-1,1),1,-1),5)

This will return the 5 largest values from col X along with the value in col B. Change the 5 at the end to however many rows you want.

I tried it but got a "SPILL!" error. Any ideas?

 

[Fluff]

Clear all cells below & to the right of the formula

 

[MetLife]

Clear all cells below & to the right of the formula

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.