Row and column labels of ranked data

Hi all,

I have a table with data (A1:C16) of which I have ranked the top 15 (F2:F16). Now, I would like columns H and J to indicate the respective row and column labels for these top values. I guess this should be possible with match/index in an array function, but I cannot get it done. Can somebody help? Many thanks!

Hi,

In H2

=INDEX($A$2:$A$16,IFERROR(MATCH(F2,$B$2:$B$16,0),MATCH(F2,$C$2:$C$16,0)))

copied down will return the row label,

and in I2

=INDEX($B$1:$C$1,MATCH(F2,OFFSET($B$2:$C$2,MATCH(H2,$A$2:$A$16,0)-1,0),0))

copied down will return the column label.

As far as I'm concerned, you don't need an array formula.

To figure out the country, you can use

=INDEX($A$2:$A$16,IF(ISNA(MATCH(F2,$B$2:$B$16,0)),MATCH(F2,$C$2:$C$16,0),MATCH(F2,$B$2:$B$16,0)))

To figure out the Incident number you can use

=IF(ISNA(MATCH(F2,$B$2:$B$16,0)),"Incident 2","Incident 1")

copy both formulae down.

Ah, sweep, you beat me to it. And then you used the IFERROR, to boot.

Anyway, for all you Excel 2003 users out there: sweep's solution works fine in XL 2007 and later (and, after all, the OP has indicated that s/he is using 2007), my suggestion works in all versions (even the later ones), even though a bit clumsier.

cheers

These are both array formulas and will return row and column heading respectively.

=INDEX($A$1:$A$16,MIN(IF($B$2:$C$16=F2,ROW($B$2:$C$16))))

=INDEX($A$1:$C$1,MIN(IF($B$2:$C$16=F2,COLUMN($B$2:$C$16))))