Spreadsheet: 726672d1617364993-lookup-multiple-ranges-example2
Sheet: Sheet1

 | TABLE FOR LOOKUP
 | Break Type | Break Hr | 07:30:00 | 16:00:00 | Start |  | End
 |  |  | Start Time | End Time | Lower Limit (>=) | Upper Limit (<) | Lower Limit (>=) | Upper Limit (<)
 | B1 | 0.25 | 09:30:00 | 09:45:00 | 07:30:00 | 09:45:00 | 09:30:00 | 11:30:00
 | L | 0.5 | 11:30:00 | 12:00:00 | 09:45:00 | 12:00:00 | 11:30:00 | 14:00:00
 | ML | 0.75 | 09:30:00 | 11:30:00 | 07:30:00 | 09:30:00 | 11:30:00 | 14:00:00
 | B2 | 0.25 | 14:00:00 | 14:15:00 | 12:00:00 | 14:15:00 | 14:00:00 | 16:00:00
 | EL | 0.75 | 11:30:00 | 14:15:00 | 09:45:00 | 12:00:00 | 14:00:00 | 16:00:00
 | A | 1 | 09:30:00 | 14:15:00 | 07:30:00 | 09:30:00 | 14:15:00 | 16:00:00
 | B3 | 0.25 | 16:00:00 | 16:15:00 | 14:15:00 | 16:15:00 | 16:00:00 | 18:00:00
 | C | 1.25 | 09:30:00 | 16:30:00 | 07:30:00 | 09:30:00 | 16:30:00 | 18:00:00
 | *If Day total = less than 4.5 Hours then no break type
Helper Column | Punch |  | EMP ID | QTY | Date | Time | IN/OUT | Job Time | Break Type | Raw Day Time | Helper Column 2 | Last Punch Time |  | Example Formula Hopes:
 | PUNCH IN |  | 001234-UR | 0 | 3/4/21 | 07:30:00 | 1 | 0 |  | 8.4 | 1 |  |  | =For Same Employee, Same Day , whose Raw Day Time great than 4.5; lookup last punch time falls in between Lookup table Start Lower + Upper limits and Time falls in between Lookup table End Lower & Upper Limits return Break Type
 | L515801 |  | 001234-UR | 0 | 3/4/21 | 10:15:00 | 2 | 2.75 | B1 | 8.4 | 2 | 07:30:00
 | 5-S |  | 001234-UR | 0 | 3/4/21 | 10:50:00 | 2 | 0.58 |  | 8.4 | 3 | 10:15:00 |  | SAMPLE of Lookup
 | L515801 |  | 001234-UR | 362 | 3/4/21 | 13:56:00 | 2 | 3.1 | L | 8.4 | 4 | 10:50:00 |  | IF(AND([@[Last Punch Time]]>=STARTLOWERLIMIT,[@[Last Punch Time]]<STARTUPPERLIMIT,[@Time]>=ENDLOWERLIMIT,[@Time]<ENDUPPERLIMIT),Instructions!$J$3,IF(AND([@[Last Punch Time]]>=Instructions!$M$4,[@[Last Punch Time]]<Instructions!$N$4,[@Time]>=Instructions!$O$4,[@Time]<Instructions!$P$4),Instructions!$J$4,IF(AND([@[Last Punch Time]]>=Instructions!$M$6,[@[Last Punch Time]]<Instructions!$N$6,[@Time]>=Instructions!$O$6,[@Time]<Instructions!$P$6),Instructions!$J$6,IF(AND([@[Last Punch Time]]>=Instructions!$M$9,[@[Last Punch Time]]<Instructions!$N$9,[@Time]>=Instructions!$O$9,[@Time]<Instructions!$P$9),Instructions!$J$9,""))))
 | 75K863 |  | 001234-UR | 800 | 3/4/21 | 15:50:00 | 2 | 1.9 | B2 | 8.4 | 5 | 13:56:00
 | 5-S |  | 001234-UR | 0 | 3/4/21 | 15:54:00 | 2 | 0.07 |  | 8.4 | 6 | 15:50:00
 | PUNCH IN |  | 002345-DR | 0 | 3/4/21 | 07:30:00 | 1 | 0 |  | 8.45 | 1
 | [NAME] |  | 002345-DR | 1050 | 3/4/21 | 15:45:00 | 2 | 8.25 | A | 8.45 | 2 | 07:30:00
 | 5-S |  | 002345-DR | 0 | 3/4/21 | 15:57:00 | 2 | 0.2 |  | 8.45 | 3 | 15:45:00
 | PUNCH IN |  | 003456-ML | 0 | 3/4/21 | 07:30:00 | 1 | 0 |  | 8.35 | 1
 | 75K863 |  | 003456-ML | 3156 | 3/4/21 | 15:51:00 | 2 | 8.35 | A | 8.35 | 2 | 07:30:00
 | PUNCH IN |  | 004567-TC | 0 | 3/4/21 | 07:30:00 | 1 | 0 |  | 8.33 | 1
 | [NAME] |  | 004567-TC | 1100 | 3/4/21 | 12:55:00 | 2 | 5.42 | ML | 8.33 | 2 | 07:30:00
 | L515801 |  | 004567-TC | 405 | 3/4/21 | 15:50:00 | 2 | 2.92 | B2 | 8.33 | 3 | 12:55:00
 | PUNCH IN |  | 005678-SA | 0 | 3/4/21 | 11:44:00 | 1 | 0 |  | 4.12 | 1
 | 75K863 |  | 005678-SA | 3000 | 3/4/21 | 15:51:00 | 2 | 4.12 |  | 4.12 | 2 | 11:44:00
 | PUNCH IN |  | 006789-HT | 0 | 3/4/21 | 07:30:00 | 1 | 0 |  | 4 | 1
 | GE0952100 |  | 006789-HT | 275 | 3/4/21 | 09:02:00 | 2 | 1.53 |  | 4 | 2 | 07:30:00
 | GR0950100 |  | 006789-HT | 500 | 3/4/21 | 10:30:00 | 2 | 1.47 |  | 4 | 3 | 09:02:00
 | GR0950000 |  | 006789-HT | 400 | 3/4/21 | 11:30:00 | 2 | 1 |  | 4 | 4 | 10:30:00