ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Can an IF statement be used for 3 options? (https://www.excelbanter.com/excel-discussion-misc-queries/14266-can-if-statement-used-3-options.html)

Jambruins

Can an IF statement be used for 3 options?
 
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.

Jason Morin

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


Myrna Larson

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.



Jambruins

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



Jason Morin

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


.


Myrna Larson

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




Jambruins

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






All times are GMT +1. The time now is 03:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com