# Using INDEX & MATCH With MAX And LARGE

Mar 31, 2008I have an Excel sheet on which I need to calculate the largest, 2nd largest and 3rd largest volumes from the range of data.

I've done this using an INDEX and MATCH formula to return the Status # for the largest, 2nd and 3rd largest volumes. So the formulas are:

Rank 1

=INDEX(B3:G3,MATCH(MAX(B3:G3),B3:G3,0))

Rank 2

=INDEX(B3:G3,MATCH(LARGE(B3:G3,2),B3:G3,0))

Rank 3

=INDEX(B3:G3,MATCH(LARGE(B3:G3,3),B3:G3,0))

However when the volumes are the same the formula returns the same Status # for both. For example below, for Site 1; Status 3 is ranked 1st and then Status 1 is ranked 2nd, for the 3rd rank, the formula is returning Status 1 again.

Is there any way of changing the formula to return Status 4 as the 3rd ranked instead of returning Status 1 again?

Status 1Status 2Status 3Status 4Status 5Status 6Rank 1Rank 2Rank 3Site 18655802Status 3Status 1Status 1Site 241818691Status 2Status 2Status 5Site 389126220Status 5Status 3Status 2Site 43591872Status 4Status 3Status 5Site 51650015Status 1Status 2Status 2