Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Wildcards in formulas?
Hi,
I have the following formula: =IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AN D(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! |
#2
|
|||
|
|||
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 "Slagmendoza" wrote: Hi, I have the following formula: =IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AN D(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! |
#3
|
|||
|
|||
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) "Slagmendoza" wrote: Hi, I have the following formula: =IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AN D(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! |
#4
|
|||
|
|||
Peo - I like your version MUCH better
"Peo Sjoblom" wrote in message ... 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 "Slagmendoza" wrote: Hi, I have the following formula: =IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AN D(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! |
#5
|
|||
|
|||
Peo,
Thanks for the help with this. I will try out your suggestion. Slag "Peo Sjoblom" wrote: 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 "Slagmendoza" wrote: Hi, I have the following formula: =IF(AND(Data!C8="Town",Data!C5="Smith"),0.03,IF(AN D(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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with named formula's | Excel Worksheet Functions | |||
Formulas | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |