Lookup if Time Between

Hi there!

I'm lost trying to figure out which formula to use in order to get a shift result (1 or 3) after data is entered into an "area" column and a "time" column.
In my lookup table are the 5 areas & min/max times for shift 1. I have not entered values for a shift 3 as what is not 1 is 3. Please note the "time" column is just numbers for lookup - not formatted as time.

I can apply the IFs, but am not looking for a huge nested formula to tie in the area. So I think the answer is to look up the area, then generate an IF <> ?


Any help is appreciated!
Regards,
SQ

Hi,
please try this in J3 and down:
=IF(SUM(((Table27[@area]=Table16[area])*(VALUE(Table27[@time])>=VALUE(Table16[min]))*(VALUE(Table27[@time])<=VALUE(Table16[max])))),1,3)

Hi, Belinda!
Thank you for the response & interesting formula! However, it doesn't work past the 5th row of data when I tested it (I had same issue with another variation I'd tried), it returns a #VALUE!.

When I opened that file, it looks like my original with no formulas. I've attached my copy with your formula and there are 2 cells that didn't give expected value. Maybe I should start anew?

Yes, this should work in your file as well. Please find attached

This one works great! Thank you so much!

You're welcome.