# How to use a range in an If statement

1. ## How to use a range in an If statement

I am charting when events happen. The time they happen is recorded by the hour it occurred in. For example, if the event happened at 12:30am than the time reference is 0, if it happens at 1:30am the time reference is 1, finishing with a 11:30pm event would get a 23 reference. I want to record the event times in four time periods (AM1 = 0 TO 5, AM2 = 6 TO 11, PM1 = 12 TO 17, AND PM2 = 18 TO 23). Here is the statement I want to make in words:

If cell A1 is greater than or equal to zero and less than or equal to five, then enter AM1 in cell B2, or if A1 is greater than or equal to 6 and less than or equal to 11, then enter AM2 in cell B2, A1 is greater than or equal to 12 and less than or equal to 17, then enter PM1 in cell B2, otherwise enter PM2 in B2.

 A1 B2 2 AM1 7 AM2 15 PM1 20 PM2  Register To Reply

2. ## Re: How to use a range in an If statement

This perhaps?

=LOOKUP(A1,{0,6,12,18,24},{"AM1","AM2","PM1","PM2",""})  Register To Reply

3. ## Re: How to use a range in an If statement

Rather than a multiple IF statement, you could use that table (assume it is in X1 to Y4) with a VLOOKUP function, like this:

=IF(A1<\$X\$1,"",VLOOKUP(A1,\$X\$1:\$Y\$4,2))

Hope this helps.

Pete  Register To Reply

4. ## Re: How to use a range in an If statement

Pete - that table is the results, not the lookup table, but yes, a lookup table would be the other way to go.   Register To Reply

5. ## Re: How to use a range in an If statement

I didn't look at the table very closely.

Pete  Register To Reply

6. ## Re: How to use a range in an If statement

It nearly caught me out - I'd created a VLOOKUP based on it before spotting the criteria in the line above!!!   Register To Reply

7. ## Re: How to use a range in an If statement

Thank you, that worked perfectly.  Register To Reply

8. ## Re: How to use a range in an If statement

You're welcome!   Register To Reply