CODEWITHSUNDEEP
excelexcel-formula
jmk1153
jmk1153

Reputation: 11

Trouble with nested IF statement

I'm looking for help writing an IF statement within excel. If the letter of a specific cell begins with a, b, c, I'd like it to list AB, if it begins with d-k, I'd like it to list BB, if l-r, list CB and finally if s - z, list DB.

I was able to get it working by only listing one if statement, but i'm having trouble dding in the others.

WORKED:

=IF(AND(RIGHT(A2,1)>="a",RIGHT(A2,1)<="c"),"AB")

CLEARLY THIS IS WRONG:

=IF(AND(RIGHT(A1,1)>="a",RIGHT(A1,1)<="c"),"AB"),
IF(AND(RIGHT(A1,1)>="d",RIGHT(A1,1)<="k"),"BB"),
IF(AND(RIGHT(A1,1)>="l",RIGHT(A1,1)<="r"),"CB"),
IF(AND(RIGHT(A1,1)>="s",RIGHT(A1,1)<="z"),"DB")

I'm sure this is a really simple fix too but I'm not sure how to get around it.

Thanks

Upvotes: 0

Views: 78

Answers (2)

user4039065
user4039065

Reputation:

How about a different approach?

=IFERROR( LOOKUP( CODE( UPPER(A1) ), {65,68,76,83,91}, {"AB","BB","CB","DB",""}),"")

CODE(A1) will always pick up the first character in the value passed into it (e.g. LEFT(A1, 1))

edit: added one more level to pass empty string in case > Z

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53137

You are closing off the If statements too soon.

Try

=IF(AND(Left(A1,1)>="a",Left(A1,1)<="c"),"AB",
IF(AND(Left(A1,1)>="d",Left(A1,1)<="k"),"BB",
IF(AND(Left(A1,1)>="l",Left(A1,1)<="r"),"CB",
IF(AND(Left(A1,1)>="s",Left(A1,1)<="z"),"DB", "what now?"))))

Upvotes: 0

Related Questions