Wildcard and Nested IF Statements

[madkinson]

In Excel 2013, I'm trying to create a nested IF statement using wildcards. I have data similar to the following:

PC Unit Account
CMCPX 21002400 GOOD
CMOPX 21002400 BAD
CMOPX 70021160 GOOD
CMCPX 70021178 BAD
CMOPX 70021186 GOOD
CMCPX 21001599 GOOD
CMOPX 21003601 BAD

When "PC Unit" = "CMCPX", I want the 3rd column to say "Good" whenever "Account" (col B) is equal to "2100*"
When "PC Unit" = "CMOPX", I want the 3rd column to say "Good" whenever "Account" is equal to "7002*"
Any other combination of values should be marked as "Bad".
There can only be either one of the other listed values in col A.
Since there are multiple valid ACCOUNT values, I tried to use a"*" as a wildcard.

Try as I might, I cannot get a valid IF statement to work. I thought about using an INDEX/MATCH, but I don't know how to put the values into the INDEX. I've always referred to ranges instead.

I really would appreciate some assistance on this as the spreadsheet has about 55,000 rows and manually spotting bad values takes a REALLY long time.

Thanking you in advance,
Mark

 

[igold]

Hi,

How about this in Cell C2 and copied down:

=IF(AND(A2="CMCPX",VALUE(LEFT(B2,4))=2100),"GOOD",IF(AND(A2="CMOPX",VALUE(LEFT(B2,4))=7002),"GOOD","BAD"))

If you have that many rows, you may want to leave the "GOOD" out and replace it with a blank "" so that just the "BAD" stand out when you scan the column...

igold

 

[Ron Coderre]

Using your sample data:
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

PC Unit​

[/td][td]

Account​

[/td][/tr]

[tr][td]

2​

[/td][td]

CMCPX​

[/td][td]

21002400​

[/td][/tr]

[tr][td]

3​

[/td][td]

CMOPX​

[/td][td]

21002400​

[/td][/tr]

[tr][td]

4​

[/td][td]

CMOPX​

[/td][td]

70021160​

[/td][/tr]

[tr][td]

5​

[/td][td]

CMCPX​

[/td][td]

70021178​

[/td][/tr]

[tr][td]

6​

[/td][td]

CMOPX​

[/td][td]

70021186​

[/td][/tr]

[tr][td]

7​

[/td][td]

CMCPX​

[/td][td]

21001599​

[/td][/tr]

[tr][td]

8​

[/td][td]

CMOPX​

[/td][td]

21003601​

[/td][/tr]
[/table]

This formula, copied down, returns GOOD if either combination applies, otherwise BAD

C2: =IF(SUMPRODUCT(COUNTIFS(A2,{"CMCPX";"CMOPX"},B2,{"2100*";"7002*"})),"GOOD","BAD")

Alternatively, you could put your criteria in a separate range:
[Table="class: grid"][tr][td] [/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

PC Unit​

[/td][td]

Account​

[/td][/tr]

[tr][td]

2​

[/td][td]

CMCPX​

[/td][td]

2100*​

[/td][/tr]

[tr][td]

3​

[/td][td]

CMOPX​

[/td][td]

7002*​

[/td][/tr]
[/table]

and use this formula

C2: =IF(SUMPRODUCT(COUNTIFS(A2,$E$2:$E$3,B2,$F$2:$F$3)),"GOOD","BAD")

For your data, these are the results, using either formula:
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]

PC Unit​

[/td][td]

Account​

[/td][td]

​

[/td][/tr]

[tr][td]

2​

[/td][td]

CMCPX​

[/td][td]

21002400​

[/td][td]

GOOD​

[/td][/tr]

[tr][td]

3​

[/td][td]

CMOPX​

[/td][td]

21002400​

[/td][td]

BAD​

[/td][/tr]

[tr][td]

4​

[/td][td]

CMOPX​

[/td][td]

70021160​

[/td][td]

GOOD​

[/td][/tr]

[tr][td]

5​

[/td][td]

CMCPX​

[/td][td]

70021178​

[/td][td]

BAD​

[/td][/tr]

[tr][td]

6​

[/td][td]

CMOPX​

[/td][td]

70021186​

[/td][td]

GOOD​

[/td][/tr]

[tr][td]

7​

[/td][td]

CMCPX​

[/td][td]

21001599​

[/td][td]

GOOD​

[/td][/tr]

[tr][td]

8​

[/td][td]

CMOPX​

[/td][td]

21003601​

[/td][td]

BAD​

[/td][/tr]
[/table]
Is that something you can work with?

 

[igold]

Hey Ron,

First I want to thank you very much.

On a MS community site you posted a solution to the problem when all un-formatted cells in a Spreadsheet suddenly default to a date format. That problem had been bugging me for months on a spreadsheet that I did not want to re-create.

For this post, in an effort to work with different solutions to the same problem I tried yours out. My results are coming back as all False or "Bad". I copied it twice with the same results. Any thoughts...

Regards,

igold

 

[Ron Coderre]

I think the accounts need to be text, not numeric, for the wildcard to engage properly.
Are your account values text?

 

[igold]

Actually, I tried both ways with the same result.

I guess one way to know would be to see what the OP comes up with...

 

[Ron Coderre]

Something must be different....I copied the example and the formulas directly from my Excel workbook.

Can you copy the example from my post, including the formulas, and see if the example returns the correct values on your computer?

 

[madkinson]

Ron:

Your first solution works so I'm going with it as I don't want to create a separate range. Thanks so much for both. Now I have to sit down and work through it so I can understand for the next time I need it.

IGold, thanks too for your solution.

 

[madkinson]

I did have to change the cell format to TEXT on the column for ACCOUNT for it to work.

 

[igold]

OK, I just copied your post from the top section and the bottom section (two separate sheets) of the post, then copied your formula into C2 and it is still coming back as all false. I am pretty sure I have not copied wrong or done something easy (read as stupid), it just does not want to return anything but false...