=if function (mutiples)

[nkll]

this should be easy (i think)

A1=can be equal to Coach, Player, Owner

B1 =if(A1="coach",1) if(A1="player",2) if(A1="owner"3)

My question is...how do you combine these "if" statements...use an "or" ????

any help is appriciated...thanks

 

[Derek]

Hi there

try

B1 =if(A1="coach",1, if(A1="player",2, if(A1="owner",3,"")))

regards
Derek

 

[Jay Petrulis]

Hi,

Try the following...

=IF(A1="Coach",1,IF(A1="Player",2,3))
=IF(A1="Coach",1,IF(A1="Player",2,IF(A1="Owner",3,"")))
=MATCH(A1,{"Coach","Player","Owner"})

 

[Yogi Anand]

Hi nkll:

I think Jay's contribution using the MATCH function is very compact -- however I will use it with the 0 argument added -- as in ...

=MATCH(A1,{"Coach","Player","Owner"},0)

to get a corrsponding result of 1, 2, or 3 depending on whether the entry in A1 is Coach, Player, or Owner.

 

[nkll]

Hi,

Try the following...

=IF(A1="Coach",1,IF(A1="Player",2,3))
=IF(A1="Coach",1,IF(A1="Player",2,IF(A1="Owner",3,"")))
=MATCH(A1,{"Coach","Player","Owner"})

Thanks.....=IF(A1="Coach",1,IF(A1="Player",2,3))
this worked

 

[Jay Petrulis]

Hi,

Try the following...

=IF(A1="Coach",1,IF(A1="Player",2,3))
=IF(A1="Coach",1,IF(A1="Player",2,IF(A1="Owner",3,"")))
=MATCH(A1,{"Coach","Player","Owner"})

Thanks.....=IF(A1="Coach",1,IF(A1="Player",2,3))
this worked

Just be careful on this one. If the cell is blank or has anything other than the options listed, this formula will default to 3.