Finding if a time lies within time range, then returning values from the corresponding row

[BBxcl]

Hi,

I have a big table of entries (called table1) that has Employee shift records. The table has Employee IDs, shift start times, shift end times and the income they have.

I want to create a spreadsheet that allows a user to enter an enquiry time during the day and it will return the highest income at that time and the corresponding employee ID.

The attached image is an example of some sample data.

I tried using the following formula to calculate the max income but get a #REF error:
=MAX(INDEX(Table1[Income],SUMPRODUCT(([@[Enquiry Time]]<Table1[End Time])*([@[Enquiry Time]]>Table1[Start Time])*(ROW(Table1[Income])))))

I have no idea what’s wrong and how I would calculate the employee ID of the highest earning employee as well.

Is anyone able to help with this?

 

[Fluff]

How about

=INDEX(SORT(FILTER(Table1,(Table1[Start Time]<=F2)*(Table1[End Time]>=F2)),4,-1),1,{4,1})

but this will need to go in a normal range, not a table.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H
1	ID	Start	End	Income		Time	Highest	ID
2	235	09:00:00	10:00:00	200		09:30	230	236
3	236	09:15:00	10:00:00	230
4	238	10:20:00	10:50:00	300
5	235	11:00:00	11:30:00	80
Sheet1

Cell Formulas
Range	Formula
G2	G2	=MAXIFS(D2:D5,B2:B5,"<="&F2,C2:C5,">="&F2)
H2	H2	=INDEX(A2:A5,MATCH(G2,D2:D5,0))