IF statement and text

[bob]

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.

 

[T. Valko]

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.

 

[ryguy7272]

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--

 

[Jacob Skaria]

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

 

[bob]

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

 

[Jacob Skaria]

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