Nested If And statements

[sadhya]

Hello, I've searched and tried for about an hour. I'm sure it's easy, but I can't see it! Can anyone help me fix the following?

=IF(AND(A43="XX23",BA43="F",BB43="W3X2H"),"T01",IF(AND(A43="XX23",BA43="F",BB43="W3X2MS"),"T02",IF(AND(A43="XX23",BA43="F",BB43="W3X2M"),"T03",IF(AND(A43="XX23",BA43="F",BB43="W3X2L"),"T04",IF(AND(A43="XX23",BA43="F",BB43="W3X2A"),"T05")))))

I believe that the logic is correct - based on the values in the three cells: A43,BA43 and BB43, I need to bring in the appropriate code: T01 through T05.

Thank you very much!

 

[Eric W]

First, it would help if you could explain what is not working about your formula.

Next, check to see if the conversion table is correct. Based on your formula, I believe that the following combinations of A43, BA43, and BB43 are what you are looking for.

If so, it appears that AA43 must always be "XX23" and BA43 must always be "F". If so, you can rewrite your formula looking at those 2 parts in a single AND, and then use the same structure you used for the BB43 piece. This version I put in Cell E2.

However, you can simplify that a bit by using MATCH instead of multiple IFs. This version is in E3.

Finally, if you have Excel 2016 with the new SWITCH function, you can shorten it even more with the version in E4.

Let me know if this helps.

	A	B	C	D	E
XX23	F	W3X2H	T01
XX23	F	W3X2MS	T02	T03
XX23	F	W3X2M	T03	T03
XX23	F	W3X2L	T04	T03
XX23	F	W3X2A	T05

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(AND(A43="XX23",BA43="F"),IF(BB43="W3X2H","T01",IF(BB43="W3X2MS","T02",IF(BB43="W3X2M","T03",IF(BB43="W3X2L","T04",IF(BB43="W3X2A","T05","No match"))))),"No match")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=IFERROR(IF(AND(A43="XX23",BA43="F"),INDEX({"T01","T02","T03","T04","T05"},MATCH(BB43,{"W3X2H","W3X2MS","W3X2M","W3X2L","W3X2A"},0)),NA()),"No match")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IF(AND(A43="XX23",BA43="F"),SWITCH(BB43,"W3X2H","T01","W3X2MS","T02","W3X2M","T03","W3X2L","T04","W3X2A","T05","No match"),"No match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sadhya]

Thank you, Eric!
That totally worked.
My bad, I should have put in what wasn't working! When I had completed the earlier formula, and hit the enter key, it still displayed in the cell in the form of a formula, instead of a value, #N/A, or #REF. BUT, that formula was referencing cells that had vlookup content instead of static values. At one point, I had tested on a line that had actual values, but I don't think I had done so with my last version. Your formula is also still displaying as a formula rather than result in the line where it is referencing vlookup content rather than values, but is working great in a test line that has values.
So, THANK YOU, now that I have something that I know works, and that requires values, I will save as values before I expect it to work.
Thank you thank you.

 

[Eric W]

I'm glad you got it working!

One other thought: if the formula is showing up as a formula in the cell, and not as the result you expect, you might want to check to see if the cell is formatted as Text. If so, change the format back to General, or some other non-Text format.

 

[sadhya]

Hah!, now it IS working referencing vlookuped cells. So, your formula is perfect completely. YAY!

 

[sadhya]

Thank you. Another point that I didn't know and could be at work here or on another sheet. I bow to your excel knowledge and thank you!

 

[Rhodie72]

Breaking down your formula to components:

1st tier:
IF AND(A43="XX23",BA43="F",BB43="W3X2H")
 = true then 
"T01" 
=false then
2nd tier

2nd tier:
IF(AND(A43="XX23",BA43="F",BB43="W3X2M")
= TRUE THEN
"T02"
=FALSE THEN
[COLOR=#ff0000]3RD TIER
[/COLOR]
3RD TIER:
IF(AND(A43="XX23",BA43="F",BB43="W3X2M")
=TRUE THEN
"T03"
= FALSE THEN
4th tier

4th tier:
IF(AND(A43="XX23",BA43="F",BB43="W3X2L")
=TRUE THEN
"T04"
=FALSE THEN
5TH TIER

5TH TIER
IF(AND(A43="XX23",BA43="F",BB43="W3X2A")
=TRUE THEN
"T05"
=FALSE THEN
ANY

 

[sadhya]

Thank you, Rhodie, Yes!, it is much clearer in the tier view, and you have the logic completely correct. I will follow this protocol for future postings. Thank you!