Help! A "logical" question :)

[Guest]

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

 

[Niek Otten]

=IF(LOWER(LEFT(A1,4))="dean","ADK",IF(LOWER(LEFT(A1,4))="dave","DJM","MWP"))

 

[Bill Martin]

I think I am making this more difficult than it is, but I can't sort it out
in my mind. I need a formula that does the following:
Returns "ADK" if the ref cell contains Dean, dean, Dean's, dean's or returns
"DJM" if the same ref cell contains Dave, dave, Dave's, dave's, and returns
"MWP" if there is anything else but Dean or Dave or David in the cell. I'm
thinking that I can use the logic if it finds an e in the 2nd position,
return "ADK" or if it finds an a in the 2nd position then return "DJM". Am I
on the right track with this? It seems like there are too many logigals for
an if/then formula?
Any help would be GREATLY appreciated! (I am using Excel 2000)

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A1,4))="DAVE","DJM","MWP"))

Bill

 

[Niek Otten]

Hi Bill,

You can see I'm lazier than you are: I tried to avoid holding the SHIFT key

 

[Bill Martin]

-------------------------

How about:

=if(ucase(left(A1,4))="DEAN","ADK",if(ucase(left(A1,4))="DAVE","DJM","MWP"))

Bill

-------------------

Oops -- for a formula, replace the "ucase" with "upper". I'm confusing
formula words with VBA words. Sorry... (and why are they different
anyhow?)

Bill

 

[Guest]

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})*{1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[JE McGimpsey]

So should "David" return DJM, too (based on "anything else but Dean or
Dave or David")?

Should "Deanna" return MWP?

If so, this should work for you:

=IF(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dean ","ADK",
IF(OR(LEFT(SUBSTITUTE(A1&" ","'s",""),5)="dave ",A1="David"),
"DJM","JWM"))

 

[JE McGimpsey]

Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.

 

[Guest]

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

 

[Guest]

Thank you for your reply! It steered me in the right direction and saved me
oodles of time!

 

[Guest]

Thank you... I will try it

If the cell can contain the specified text anywhere in the cell, like in the
following examples:
ADean
Ben dean bean
The Dean's list
hi Dave
to dave and ben
is this David?

Then, try this:
For text in A1
B1:
=CHOOSE(SUMPRODUCT(COUNTIF(A1,{"*dean*","*dav*"})*{1,2})+1,"MWP","ADK","DJM","Has both!")

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Guest]

Thank you for your time... I will try this, too...

 

[Niek Otten]

Absolutely right, JE!

Thanks,

Niek

Just a note - the UCASE()'s are unnecessary in this formula since XL's
comparisons are case insensitive.

See my other post for a different answer - the OP's problem statement
seemed rather ambiguous.