Too many IFs, two criteria, and nine possible outcomes

[fbrb99]

Hello everyone,

First, this site is fantastic and has helped me become very well versed in Excel. So thank you.

For my problem, I've searched the forums for a couple of hours and although I have a general idea of a solution, I'm still not getting it. I have attached a scrubbed Excel file for help.

The problem:
I need to populate a column (Sheet2, column G) based on the values in two other columns (Sheet1, columns E and J). There are nine specific combinations that come up in columns E and J that I need to provide a unique code for in column G.

Because there are more than seven, this rules out using nested IF statements. If I could use IFs, the formula in cell G3 would look like this:

IF(AND(Sheet1!E10=0,Sheet1!J10<>0),"S",
IF(AND(Sheet1!E10<>0,Sheet1!J10=0),"B",
IF(AND(Sheet1!E10=0,Sheet1!J10=0),"SB",
IF(AND(Sheet1!E10="Probe",Sheet1!J10<>0),"S-Probe",
IF(AND(Sheet1!E10<>0,Sheet1!J10="Probe"),"B-Probe",
IF(AND(Sheet1!E10="Probe",Sheet1!J10=Probe),"SB-Probe",
IF(AND(Sheet1!E10=0,Sheet1!J10="Probe"),"S,B-Probe",
IF(AND(Sheet1!E10="Probe",Sheet1!J10=0),"S-Probe,B",
IF(AND(Sheet1!E10<>0,Sheet1!J10<>0),"")))))))))

I think I need VLOOKUP, but I haven't used it enough to know how to use it in this situation.

Thank you very much in advance for your help.

[NBVC]

Does this work?

Please Login or Register  to view this content.

[fbrb99]

Hi NBVC,

I like the formula because it looks like a way to combine IF statements I've never used before.

But the formula as-is is not giving me the right responses. For example, E10=0 and J10=0, so I want the formula to produce the response "SB", but it is producing the response "S".

I would still appreciate other possible solutions or variations on the formula you provided. Thanks again.

[NBVC]

I forgot a few sheet1 references before the J10's...

try:

Please Login or Register  to view this content.

[fbrb99]

Yes Yes Yes!!!

That did it. Thank you so much for helping me solve the problem.

And again, you've taught me a new way to use IFs so I also appreciate the learning.

If I am reading it right, it looks like you used an IF statement in the value_if_true field which I had never seen or thought of before. It seems like it allows you to fold in more than seven possible "then" responses.

Thanks again! Have a great weekend.