Can I setup an IF statement to select between 3 options? For example, I have
3 divisions with 5 teams in each division. Can I setup a formula to have it
enter the proper division if I have the team name entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6. Thanks.

Assuming your division names are in A1:C1 and teams are
in the 5 rows below each division (A2:C6), then try:

=OFFSET(divisions!A1,,MAX(IF(ISNUMBER(SEARCH(B2,di visions!
A1:C6)),COLUMN(divisions!A1:C6)))-1,)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I setup an IF statement to select between 3
options? For example, I have
3 divisions with 5 teams in each division. Can I setup
a formula to have it
enter the proper division if I have the team name
entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL
EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and
A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6.
Thanks.
.

I would set up a table with the Team name in the 1st column and the division
in the 2nd. Say you put this in K1:L15. Note that the single table contains
all teams from all divisions.

With BOS in B2, in C2 you write the formula =VLOOKUP(B2,$K$1:$L$15,2,0)


On Mon, 21 Feb 2005 12:41:05 -0800, Jambruins
wrote:

Can I setup an IF statement to select between 3 options? For example, I have
3 divisions with 5 teams in each division. Can I setup a formula to have it
enter the proper division if I have the team name entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6. Thanks.

I get a #value! when I enter that. Any other ideas. My division names are
in A1:C1 and the teams are in the five rows below like you assumed. The team
names are in cells B2:B62. Thanks.

"Jason Morin" wrote:

Assuming your division names are in A1:C1 and teams are
in the 5 rows below each division (A2:C6), then try:

=OFFSET(divisions!A1,,MAX(IF(ISNUMBER(SEARCH(B2,di visions!
A1:C6)),COLUMN(divisions!A1:C6)))-1,)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I setup an IF statement to select between 3
options? For example, I have
3 divisions with 5 teams in each division. Can I setup
a formula to have it
enter the proper division if I have the team name
entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL
EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and
A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6.
Thanks.
.

It works for me. Remember to press ctrl + shift + enter.
If you're going to copy the formula down, you'll need to
make the references absolute:

=OFFSET(divisions!$A$1,,MAX(IF(ISNUMBER(SEARCH
(B2,divisions!$A$1:$C$6)),COLUMN(divisions!$A$1:$C $6)))-
1,)

HTH
Jason
Atlanta, GA

-----Original Message-----
I get a #value! when I enter that. Any other ideas. My
division names are
in A1:C1 and the teams are in the five rows below like
you assumed. The team
names are in cells B2:B62. Thanks.

"Jason Morin" wrote:

Assuming your division names are in A1:C1 and teams
are
in the 5 rows below each division (A2:C6), then try:

=OFFSET(divisions!A1,,MAX(IF(ISNUMBER(SEARCH
(B2,divisions!
A1:C6)),COLUMN(divisions!A1:C6)))-1,)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I setup an IF statement to select between 3
options? For example, I have
3 divisions with 5 teams in each division. Can I
setup
a formula to have it
enter the proper division if I have the team name
entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL
EAST. I have a tab called
divisions with the divisions listed in cells A1, A2,
and
A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and
A6.
Thanks.
.


.

You said 3 divisions, each with 5 teams. That's 15 teams total. But you refer
to team names in B2:B62 ????

On Mon, 21 Feb 2005 13:15:07 -0800, Jambruins
wrote:

I get a #value! when I enter that. Any other ideas. My division names are
in A1:C1 and the teams are in the five rows below like you assumed. The team
names are in cells B2:B62. Thanks.

"Jason Morin" wrote:

Assuming your division names are in A1:C1 and teams are
in the 5 rows below each division (A2:C6), then try:

=OFFSET(divisions!A1,,MAX(IF(ISNUMBER(SEARCH(B2,di visions!
A1:C6)),COLUMN(divisions!A1:C6)))-1,)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I setup an IF statement to select between 3
options? For example, I have
3 divisions with 5 teams in each division. Can I setup
a formula to have it
enter the proper division if I have the team name
entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL
EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and
A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6.
Thanks.
.

thanks for your help everyone, I have solved my problem

"Myrna Larson" wrote:

You said 3 divisions, each with 5 teams. That's 15 teams total. But you refer
to team names in B2:B62 ????

On Mon, 21 Feb 2005 13:15:07 -0800, Jambruins
wrote:

I get a #value! when I enter that. Any other ideas. My division names are
in A1:C1 and the teams are in the five rows below like you assumed. The team
names are in cells B2:B62. Thanks.

"Jason Morin" wrote:

Assuming your division names are in A1:C1 and teams are
in the 5 rows below each division (A2:C6), then try:

=OFFSET(divisions!A1,,MAX(IF(ISNUMBER(SEARCH(B2,di visions!
A1:C6)),COLUMN(divisions!A1:C6)))-1,)

Array-entered, meaning press ctrl + shift + enter.

HTH
Jason
Atlanta, GA

-----Original Message-----
Can I setup an IF statement to select between 3
options? For example, I have
3 divisions with 5 teams in each division. Can I setup
a formula to have it
enter the proper division if I have the team name
entered. For example, if I
have BOS in cell B2 how do I get cell C2 to say AL
EAST. I have a tab called
divisions with the divisions listed in cells A1, A2, and
A3. The teams from
division A1 are listed in cells A2, A3, A4, A5, and A6.
Thanks.
.