Excel Match?

[CommSecy]

I have two (2) separate columns: as an example column E3 is 7:00, column F3 is 16:00. I want to lookup and match BOTH of these entries to a table that contains various hours with the third column producing a differential that is due if matched. Ex:

07:00	16:00	0
07:00	16:00	1
07:00	17:00	1
07:00	19:00	4

So in the case above that employee would be due 0 differential. I have tried index match but apparently this formula is ABOVE my skill level. I cannot combine cells to match as these numbers change every two weeks and it would be labor intensive and take up unnecessary time.

Would appreciate any help!!!

 

[Noxqss38242]

Can you provide an example of what an end result would look like?
If matched the differential is the same or does the differential change each time? What is the criteria if not the same?
Is the table within the same workbook?

 

[CommSecy]

Can you provide an example of what an end result would look like?
If matched the differential is the same or does the differential change each time? What is the criteria if not the same?
Is the table within the same workbook?

well like 0700 - 1900 would result in 4 if that is matched. there are over 18 differentials that would be in the table to be matched. The differential does not change, just the daily hours of the employee change. one day he could work 0700-1600 which would result in 0 hours (there is a mistake in the above table). if he works 0700-1700 he would receive 1hr, etc.

 

[CommSecy]

here is what I am trying (unsuccessfully) to do...so i want to have the answer in the g column to each entry. look up table H2>J14. the J column is the night diff for each shift. so if e1 AND f1 match then it should return the corresponding night diff. Hope I explained this better and gave a decent picture of what I am trying to accomplish.

A1	B1	C1	D1	E1	F1	G1	LOOK UP TABLE
	LastName	FirstName	Date	StartTime	EndTime	Night Diff	05:00	14:00	2
A3	Smith	John	4/14/2022​	7:00​	16:00​		05:00	17:00	3
A4	Smith	John	4/16/2022​	7:00​	19:00​		06:00	14:30	1
A5	Smith	John	4/12/2022​	13:00​	22:00​		06:00	18:00	3
A6	Smith	John	4/19/2022​	9:00​	19:00​		07:00	17:00	1
A7	Smith	John	4/14/2022​	16:00​	1:00​		07:00	19:00	4
A8	Smith	John	4/17/2022​	19:00​	7:00​		08:00	18:00	2
A9	Smith	John	4/12/2022​	22:00​	7:00​		09:00	19:00	3
A10	Smith	John	4/14/2022​	11:00​	23:00​		11:00	23:00	8
A11	Smith	John	4/13/2022​	14:00​	0:00​		13:00	22:00	7
A12	Smith	John	4/19/2022​	7:00​	17:00​		13:00	01:00	10
A13	Smith	John	4/15/2022​	5:00​	14:00​		14:00	23:00	9
A14	Smith	John	4/16/2022​	5:00​	17:00​		14:00	00:00	10
I WANT TO PUT A FORMULA IN THE G3 THROUGH G14 CELLS WHICH WILL LOOK UP IF E1 AND F1 MATCH IN THE LOOK-UP TABLE THE RESULT WILL BE THE CORRESPONDING ANSWER IN J2 THROUGH J18	16:00	01:00	9

AGAIN thank you!!!