As long as the ranks are unique try this...

Entered in G1 and copied down as needed:

=INDEX(B$1:B$8,MATCH(SMALL(F$1:F$8,ROWS(G$1:G1)),F $1:F$8,0))

If there might be duplicate ranks then it gets more complicated.

--
Biff
Microsoft Excel MVP


"bob" wrote in message
...
Columns A:F look like this:

1 Atlanta 45 37 110.4 1
2 Boston 57 25 107.6 3
3 Charlotte 23 59 102.5 6
4 Chicago 44 38 103.6 5
5 Cleveland 56 26 109 2
6 Dallas 50 32 99 8
7 Denver 54 28 100.8 7
8 Detroit 39 43 105.4 4

Using an array,
From G1, I want to return the value in B1:B8 if F1:F8 = 1
From G2, I want to return the value in B1:B8 if F1:F8 = 2
From G3, I want to return the value in B1:B8 if F1:F8 = 3
...and so on.

The correct answers in G1, G2 and G3 should be Atlanta, Cleveland, and
Boston, respectively.