# Using Wildcards in IF statements

1. ## Using Wildcards in IF statements

Let's presume that a spreadsheet has 3 columns: A, B, C

In Col A & Col B there is text. The text for both these columns can be any of these 3 items from this list: Yes or Yes*C* or No

Column C is to hold a formula that will enter text into that column C depending on a search/comparison of the text in Col A and Col B

I tried to use wildcards to match on members of the list, without success.

Now I see in some forums that people have suggested other methods to do this comparison such as: =IF(AND(COUNTIF(A3:A3,"Y*"),COUNTIF(B3:B3,"N*")),"Adv")

I would like to understand why formula #1: If(And(A3="Y*",B3="No"),"Adv") does not work and why and formula#2: =IF(AND(COUNTIF(A3:A3,"Y*"),COUNTIF(B3:B3,"N*")),"Adv") does work since this is not intuitive to me and there clearly are some tricks to understand when using formulas and wildcards. The use of countif to me seems to be a bit overkill and I obviously think (albeit incorrectly) that formula#1 should work.

I'm going to take a shot at why countif works and hope that I understand why it does but I clearly would love to hear comments on what's going on. I believe countif works in this scenario because since my range is limited to one cell, the result of the match will return a "1". Returning a "1" will be interpreted as a True by the surrounding IF statement and therefore the function will complete and acheive the result desired. Is that correct? But why doesn't formula#1 work as well?

Are there any other considerations or tricks when doing this type of comparison/calculation on a text string?

Hopefully this discussion will also be useful to a wide audience too.

Regards,
michael

PS. I've attached a test xls file with a very similiar example of what I am trying to do, for reference.

2. ## Re: Using Wildcards in IF statements

For the most part, in general formulas that use = sign to compare conditions cannot use wildcards and formulas that use commas to separate conditions for comparison can.

3. ## Re: Using Wildcards in IF statements

Some functions have wildcard functionality (COUNTIF, SUMIF, MATCH etc) and others don't - it's as basic as that in truth. In your case a basic = test won't give you wildcard functionality but you could use:

4. ## Re: Using Wildcards in IF statements

You could use

=IF(H1<>0,(CONCATENATE(A1,"jobBomPrt")),"")

where <> represents not equal to 0

There are currently 1 users browsing this thread. (0 members and 1 guests)