IF/THEN Syntax issues

[EdinNH]

Hi all!

First time poster here. I'm having trouble figuring out how to write an IF/THEN statement to auto-populate a column for me.

I need cell H2 to check cell A2 and based on what it finds in A2, it needs to spit out one of four variables.

For Example:

If A2 is between 10-15, H2 = 1, If A2 is between 20-25, H2 = 2, If A2 is between 30-35, H2 = 3, If A2 is any other number outside of these ranges, H2 needs to be blank.

Thanks!

Ed

[delaneyjm]

You could use the following, but if you cannot have more than 7 if statements nested.

=IF(AND(A2>=10,A2<=15),1,IF(A2>=20,A2<=25),2,IF(AND(A2>=30,A2<=35),3,"")))

If you have more than 7, you will need to implement a lookup table.

[TMS]

One way, with the information provided:

=IF(AND(A2>=10,A2<=15),1,IF(AND(A2>=20,A2<=25),2,IF(AND(A2>=30,A2<=35),3,"")))


Regards, TMS

[stanleydgromjr]

EdinNH,

Welcome to the Excel Forum.

In cell H2 try:
=LOOKUP(A2,{0,10,16,20,26,30,36},{"",1,"",2,"",3,""})

[EdinNH]

Works like a charm! Thanks guys!

[TMS]

You're welcome. Thanks for the rep.

The LOOKUP approach is neat and scalable, while the IF/AND combination is limited.

Regards, TMS

[stanleydgromjr]

EdinNH,

Thanks for the feedback.

You are very welcome. Glad we could help.

Come back anytime.