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.
Regards,
michael
PS. I've attached a test xls file with a very similiar example of what I am trying to do, for reference.
Bookmarks