# Nested IF LEFT and RIGHT THEN

1. ## Nested IF LEFT and RIGHT THEN

Hello,

Job Codes =

EX12.01
EX24.02
EX33.03

I need a nested statement that says:

IF Job Code begins with "EX" and and ends in "1", THEN result = "TIER 1"
IF Job Code begins with "EX" and and ends in "2", THEN result = "TIER 2"
IF Job Code begins with "EX" and and ends in "3", THEN result = "TIER 3"

Thank you!  Register To Reply

2. ## Re: Nested IF LEFT and RIGHT THEN

Hello and welcome to the forum.

Assuming that your job codes are in column A starting in A2, try this:

="TIER "&IF(LEFT(A2,2)="EX",CHOOSE(RIGHT(A2),1,2,3))  Register To Reply

3. ## Re: Nested IF LEFT and RIGHT THEN

IT WORKS! Thank you very much for your quick response. Much appreciated :-)  Register To Reply

4. ## Re: Nested IF LEFT and RIGHT THEN

You must have seen it before I edited. It will not work in all scenarios. I would use the one that is now in post #2.  Register To Reply

5. ## Re: Nested IF LEFT and RIGHT THEN

Hi, thanks for the update. What if I have a job code that is EX23.1X (doesn't end in 1,2,3), how do I get it to reflect "TIER 1"? And anything that doesn't meet this requirement is "INVALID"?  Register To Reply

6. ## Re: Nested IF LEFT and RIGHT THEN

you need to give some examples and the solutions you wish

="tier"&IF(LEFT(A2,2)="EX",IFERROR(RIGHT(A2,1)*1,MID(A2,LEN(A2)-1,1)),"")

or

="tier"&IF(LEFT(A2,2)="EX",IFERROR(RIGHT(A2,1)*1,"Invalid"),"")  Register To Reply

7. ## Re: Nested IF LEFT and RIGHT THEN

Job Code TIER
EX00.01 1
EX01.02 2
EX02.03 3
EX04.7X 1
EX23.8L 1
GL64.13 INVALID

So basically, if it doesn't begin with EX, it is "INVALID". If it does begin with "EX", but doesn't end in 1,2,3, then it defaults to Tier 1. Otherwise, it is Tier 1, 2, or 3.  Register To Reply

8. ## Re: Nested IF LEFT and RIGHT THEN

Based on post #7, you can use this in B2:

=IF(LEFT(A2,2)="EX",IF(OR(RIGHT(A2)={"2","3"}),RIGHT(A2)+0,1),"INVALID")  Register To Reply

9. ## Re: Nested IF LEFT and RIGHT THEN

This exactly what I needed. Works like a charm. THANK YOU!  Register To Reply

10. ## Re: Nested IF LEFT and RIGHT THEN

You're welcome.  Register To Reply