IF statement problem in EXCEL 1

[music1111]

I want to have Excel check the conditions of two columns and place the result in another cell. The IF statements checks only one condition. Is there a way I can check two cells for a condition instead of checking only one cell?

Example: Column A can be a 1 or 2, column B can be a 1,2,or 3. 6 possible combinations. I want to place the result in column C.

Thanks,
Chris (music1111)

 

[DaleWatson123321]

Chris,

The following formula should be what you're seeking...

=IF(AND(A2=1,B2=1),"Result_1",IF(AND(A2=1,B2=2),"Result_2",IF(AND(A2=1,B2=3),"Result_3",IF(AND(A2=2,B2=1),"Result_4",IF(AND(A2=2,B2=2),"Result_5",IF(AND(A2=2,B2=3),"Result_6","&quot)))))

Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[music1111]

Dale,

Thank you! You are a life saver! It worked perfectly.

Chris

 

[Zathras]

If you would like a shorter expression, try this:

="Result_"&LOOKUP(A1&B1,{"11","12","13","21","22","23";1,2,3,4,5,6})

This should be a little easier to maintain, especially when more possible numbers are enabled for either column. Just remember to keep the concatenated values in ascending order.

 

[Zathras]

Or if you need more control over the possible results, try this:

=CHOOSE(LOOKUP(A1&B1,{"11","12","13","21","22","23";1,2,3,4,5,6}),"a","b","c","d","e","f")

"a" thru "f" can be anything you want.

 

[mscallisto]

To handle up to 3 sets of combinations I world do:
Plus with modification it should go forever.

A B C C =RIGHT(A1*10+B1*10+ROW())
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9

 

[mscallisto]

Oops I failed to read the question!
This should do what you want and never need changing.

A1 B1 C1 =(A1-1+B1+(A1/1))-1+(A1-1)
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
3 1 7
3 2 8
3 3 9
Etc.

 

[music1111]

Thanks to everyone that has posted a solution! But I have another question. I now have more combinations (12 instead of 6) thanks to a company revision so the IF/AND doesn't work anymore since you can't have more than 7 functions. I would like to try the LOOKUP functions as posted, but I don't quite understand the logic. If I am comparing A1 and B1, then what are the "11","12" fields pointing to and how do they relate to the 1,2 fields?

I have 2 columns: the first columns choices are 1 or 2, and the second columns choices are 1 thru 6, each combo needing a different result displayed.

Thanks,
Christine

 

[mscallisto]

Music1111

If the problem remains as it was except for the number of combinations, I believe the formula in my above post will work infinate combinations.

See the post with =(A1-1+B1+(A1/1))-1+(A1-1)

much luck

 

[PepeLeMokko]

You could also try creating a third column which concatenates the first two.
This would give you the "11", "12", etc. . combinations
You then create a table in another worksheet, placing all possible values ( from "11" to "26&quot in the first column and the results in the second.
Then using the Lookup function you can put the results against the two digit combination ( you can of course embed the concatenate function in the lookup function.
Reagrds
André

 

[music1111]

Again thanks for all the help so far. I am going crazy. I tried both ways but still have a problem. If I use the table with the 11,12,13,etc. and name the table and use the VLOOKUP(A1&B1),myData,2,FALSE), then it does not pick up my value.

If I use the concatation formula like =CHOOSE(LOOKUP(C11&D11,{"11","12","13","14","15","16","21","22","23","24","25","26";1,2,3,4,5,6,7,8,9,10,11,12}),".080",".069",".016",".080",".069",".016",".045",".035",".008",".045",".035",".008&quot, it picks up my correct value, but it won't multiply right in my next cell.

PLEASE HELP!
Christine

 

[mscallisto]

Music1111

I used your formula, got the correct result, example 12 = .069 then multiplied the result by 1 and got .069.

I guess I don't understand what problem you're having.
Can you provide an example of "it won't multiply right in my next cell"?

 

[xlbo]

I believe the problem has been sorted (via private email) - problem was text masquerading as numbers - therefore wouldn't sum etc Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[mscallisto]

ad extremum !