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.

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.

With data in A1:F8, put this in G1:
=INDEX($B$1:$B$8,MATCH(SMALL($F$1:$F$8+ROW($F$1:$F $8)/100,A1),$F$1:$F$8+ROW($F$1:$F$8)/100,0))

Enter with Ctrl + Shift + Enter, not just Enter.
Fill down.

HTH,
Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"bob" wrote:

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.

Hi Bob

Try the below INDEX() MATCH() combination in G1 and copy down as required

=INDEX($B$1:$B$8,MATCH(A1,$F$1:$F$8,0))

If this post helps click Yes
---------------
Jacob Skaria


"bob" wrote:

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.

I don't understand the logic behind the index and match commands but it does
work. thanks a lot.

"Jacob Skaria" wrote:

Hi Bob

Try the below INDEX() MATCH() combination in G1 and copy down as required

=INDEX($B$1:$B$8,MATCH(A1,$F$1:$F$8,0))

If this post helps click Yes
---------------
Jacob Skaria


"bob" wrote:

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.

Hi Bob

--For explanation on MATCH() check out the below link
http://www.excelfunctions.net/ExcelMatch.html

--For more examples on using both these functions refer the below link by
excel MVP Debra Dalgleish.
http://www.contextures.com/xlFunctions03.html

If this post helps click Yes
---------------
Jacob Skaria


"bob" wrote:

I don't understand the logic behind the index and match commands but it does
work. thanks a lot.

"Jacob Skaria" wrote:

Hi Bob

Try the below INDEX() MATCH() combination in G1 and copy down as required

=INDEX($B$1:$B$8,MATCH(A1,$F$1:$F$8,0))

If this post helps click Yes
---------------
Jacob Skaria


"bob" wrote:

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.