What formula to use?

[Jambruins]

I have a list of teams in column B. In a tab called Division I have 6
division names with the teams listed in their respective division. I want
column C to enter the appropriate division name for the division the team is
in.

For example, I want column C to be labelled as AL EAST when column B has BOS
in it. How do I do this? Thank you.

 

[Bob Phillips]

=INDEX(Division!A:A,MATCH(B1,Division!B:B,0))
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jambruins]

I get a #n/a when I paste that in. The divisions are in cells A1, B1, C1,
D1, E1, and F1. The teams in division A1 are listed below it in cells A2,
A3, A4, A5, and A6. All of theother divisions are setup the same way. Any
idea why the formula is nor working? Thanks

 

[Jambruins]

actually it gives me BOS now. BOS is in cell A3 on the Division Tab.

 

[Dave Peterson]

How about:

=INDEX(Division!$A$1:$F$1,
MIN(IF(B1=Division!$A$2:$F$6,COLUMN(Division!$A$2:$F$6))))

(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)