Wild card in if function

Sarah7828

New Member
Joined
Oct 25, 2024
Messages
17
Office Version
  1. 2021
Hi there

In worksheet there are two text that require a different rate

Cell A1 Trustee Fee March 2021 - IN0000

Cell B1 Bell Projects Costs Dec 23 (Fam) - IN000A

The words in Cell A1‘Trustee Fee ‘ and words in Cell B1 ‘Bell Project Cost (Fam)’ will be recurring text each month . I mind to know how I can create a wildcard with in a IF function to capture their rate

If (or(a1 =“*Trustee Fee*”, b1=“*Bell Project Cost (Fam)*”), 0.02, 0.04)

On evaluation of formula , gives me false as cell a1 is not exactly equal to “Trustee Fee” for example . How can I fix the formula ?

Thank you
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
What you're asking can be done but not the way you are expecting it to. Does this work for you?

=IF(OR(ISNUMBER(FIND("Trustee Fee",A1)),ISNUMBER(FIND("Bell Project Cost (Fam)",B1))),0.02,0.04)
 
Upvote 0
Another option might be:
Excel Formula:
=IF(OR(COUNTIFS(A1,"*Trustee Fee*"),COUNTIFS(B1,"*Bell Project Cost (Fam)*")),0.02,0.04)
Note: if CountIfs returns 0 it = False any other Value is True
 
Upvote 0
What you're asking can be done but not the way you are expecting it to. Does this work for you?

=IF(OR(ISNUMBER(FIND("Trustee Fee",A1)),ISNUMBER(FIND("Bell Project Cost (Fam)",B1))),0.02,0.04)
Hi Scott, my explanation of what I was trying to achieve wasn’t stated correctly. the recurring text will be will be in column A , but with different cell references - cell A1 could be “Trustee Fee”or A2 could be “Bell project cost(Fam)” and vice versa , with these text the rate 0.02, if I can’t find these recurring text then the rate is 0.04

So if “Trustee Fee” OR “Bell Project Cost(Admin) is in cell A1 , then the rate is 0.02

How to I adjust your formula

Thank you for you help
 
Upvote 0
Try just changing B1 to A1 in each of the earlier formulas.
You may also want to consider changing FIND to SEARCH in the first suggestion if the upper/lower case of the text being checked could be different.

I'm not certain about their use in your Excel version but you could also try
Excel Formula:
=0.04-0.02*(SUM(COUNTIFS(A1,{"*Trustee Fee*","*Bell Project Cost (Fam)*"}))>0)
Excel Formula:
=0.04-0.02*(COUNT(MATCH({"*Trustee Fee*","*Bell Project Cost (Fam)*"},A1,0))>0)
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,134
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top