Wildcards in formulas?

[Guest]

Hi,

I have the following formula:

=IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AND(Data!C8="Town",Data!C5="Jones"),0.03,0.05))

I want to change a value in a cell (to either 0.03 or 0.05) depending on
what values are in two other cells on worksheet Data. If DataC8 = "Town" AND
DataC5 = ANYTHING that contains either "Smith" OR "Jones" (could be Smith,
Smith Brothers, Smith Boys, etc, OR Jones, Jones Brothers, Jones Boys, etc.).

Is it possible to use wildcards in the above formula somehow to accomplish
this?

Thanks!

 

[Guest]

You can't use wildcards in logical functions (you can use it in lookups,
match, sumif etc), but you can get the equivalent using search/find (find if
you want it to be case sensitive like Jones and not jones)

=IF(AND(Data!C8="Town",OR(ISNUMBER(SEARCH({"Smith","Jones"},Data!C5)))),0.03,0.05)

should do it if you want 0.03 if the conditions are TRUE and 0.05 if FALSE

Regards,

Peo Sjoblom

 

[Guest]

Your current formula could be a little shorter:

=IF(AND(Data!C8="Town",or(Data!C5="Smith",Data!C5="Jones"),.03,.05)

To check for Smith at the beggining you could use LEFT(Data!C5,5)="Smith",
but that would also get you Smithereens. LEFT(Data!C5,6)="Smith " if it will
always be Smith followed bya space.

Or you could do a case sensitive test:

=IF(AND(Data!C8="Town", OR(NOT(ISERROR(FIND("Smith",Data!C5,1))),
NOT(ISERROR(FIND("Jones",Data!C5,1))))), 0.03,0.05)

 

[Duke Carey]

Peo - I like your version MUCH better

You can't use wildcards in logical functions (you can use it in lookups,
match, sumif etc), but you can get the equivalent using search/find (find
if
you want it to be case sensitive like Jones and not jones)

=IF(AND(Data!C8="Town",OR(ISNUMBER(SEARCH({"Smith","Jones"},Data!C5)))),0.03,0.05)

should do it if you want 0.03 if the conditions are TRUE and 0.05 if FALSE

Regards,

Peo Sjoblom

 

[Guest]

Peo,

Thanks for the help with this. I will try out your suggestion.

Slag