IF statement and text

  • Thread starter Thread starter bob
  • Start date Start date
B

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.
 
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.
 
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--
 
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
 
I don't understand the logic behind the index and match commands but it does
work. thanks a lot.
 
Back
Top