+ Reply to Thread
Results 1 to 4 of 4

Using Wildcards in IF statements

  1. #1
    Registered User
    Join Date
    New Jersey, USA
    MS-Off Ver
    Excel 2003

    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.

    For example in C1: If(And(A3="Y*",B3="No"),"Adv")

    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.


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

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    Mississauga, CANADA
    MS-Off Ver

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    Essex, UK
    MS-Off Ver

    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:

    C1: =IF(AND(LEFT(A3)="Y",B3="No"),"Adv","")

  4. #4
    Valued Forum Contributor
    Join Date
    United States
    MS-Off Ver
    Excel 2010

    Re: Using Wildcards in IF statements

    You could use


    where <> represents not equal to 0

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1