Multiple IF Statements / Equal to Greater than etc.

[thenotoriousjeff]

Hello - looking for assistance writing this:

IF A1 is equal to 5 but less than or equal to 20, then 3
IF A1 is equal to 21 but less than or equal to 40, then 2
IF A1 is equal to or greater than 41, then 1

Alternately, can this be done through conditional formatting? Specifically, I would like to highlight an entire row if the above range conditions apply - i don't necessarily need the 3,2,1 ranking if this is the case.

Thanks!

[AlKey]

Try this in B1

=INDEX({3,2,1},MATCH(A1,{5,21,41}))

or this

=LOOKUP(A1,{5,21,41},{3,2,1})

	A	B
1	5	3
2	21	2
3	30	2
4	41	1

[Glenn Kennedy]

I have assumed that instead of "equal to 5 but less than or equal to 20",, etc, you meant to say
"greater than or equal to 5 and less than or equal to 20"
.

Please Login or Register  to view this content.

If that's correct - this will work. If I've misunderstood you, please tell me!

[thenotoriousjeff]

Thank you Glenn - it works perfect!! Have a good day.

[Sam Capricci]

was going to add that if you want a simple if then stmt, this should do it for you for what it's worth...
=IF(AND(A1>4,A1<21),3,IF(AND(A1>20,A1<41),2,IF(A1>40,1,"")))

[Glenn Kennedy]

Cheers, Jeff (notorious or otherwise...).