Using Index & Match with the Large function

Hello All

I have a spreadsheet which contains a Currency (colA), Number (colB) and HighRate (colC).

I need to create a template so that when the data in these columns is updated then the spreadsheet will automatically do the following...

- find the largest value in colB and pull it across along with the identifiers in A and C.
- then find the second largest in B and pull across identifiers
- and so - hence sorting colB by largest to smallest value and then pulling across the corresponding identifiers.

I can do this with

=INDEX($A$3:$A$27,MATCH(LARGE($B$3:$B$27,ROW()-ROW($F$2)),$B$3:$B$27,0))

BUT it doesn't work if I have more than one value the same in ColB i.e 3 rows of the number 2 !

Any improvements to the code or suggestions to get it working would be greatly appreciated. File attached....

Thanks
JXH

Try these,

F3, copy down.

=LARGE(B:B,ROWS(F$3:F3))

E3, must hit CTRL+SHIFT+ENTER, not just ENTER. copy down

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B$3:B$21=F3,ROW(B$3:B$21)),COUNTIF(F$3:F3,F3)))))

G3, must hit CTRL+SHIFT+ENTER, not just ENTER. copy down.

=IF(E3="","",INDEX(C$3:C$21,MATCH(1,IF(A$3:A$21=E3,IF(B$3:B$21=F3,1)),0)))

Let me explain my best. I am not good to explain something.

CHOOSE{1,2},"",... used here to avoid displaying errors.

Firstly take this part.

INDEX(A:A,SMALL(IF(B$3:B$21=F3,ROW(B$3:B$21)),COUNTIF(F$3:F3,F3)))

This will give the first value from Col_A, where B3:B21=F3.
If you have more than 1 occurrences COUNTIF will check howmany times currespondent value in Col_F contains in Col_B. 1,2,3....

INDEX will give the value from Col_A, if it is an error, say if you copy the formula down after E22 will give an error. So,

Take E3, as an example.

CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B$3:B$21=F3,ROW(B$3:B$21)),COUNTIF(F$3:F3,F3))))

will give always 2 arrays, like

{"","EURAUD"}

A blank & INDEX result. If INDEX result is an error, this will be like,

{"",#NUM!} or {{"",#VALUE!}

So, we are looking here for a text <=ZZZZZ (Z is the biggest alphabet, if you use 5 Z's will give a little biggest text)

LOOKUP will avoid the error values & always return the last text, which is,

If INDEX gives an error, will be "" return a blank, otherwise INDEX result.

If you use the formula evaluator, you can understand easily than my explanation.

Hope this helps.