Cashier time log Lookup

Hi ronettes,

VLOOKUP only returns the first match it finds.

For simplicity I took the liberty of making a lookup table in column G:H and another drop down in M2 for the dates.

Then in K7:K17 this formula to return cashier #s.

Formula:

=INDEX($H$1:$H$11,MATCH(L7,$G$1:$G$11,0))

In M7:N7 this formula to return the times.

Formula:

=IFERROR(INDEX($E$3:$E$860,INDEX(MATCH({1,2},1/(($K7=$B$3:$B$860)*($M$2=$D$3:$D$860)*($L$2=$A$3:$A$860)),{0,1}),COLUMNS($M:M))),"")

This returns the start and end times, but it appears that on 7/21/2016 cashier # 70 works two shifts on till 1. The formula only returns the start of the first shift and the end of the second. Is this what you want?

Assuming that each entry represents a sale, try pasting this formula in O7 and copy down:

Formula:

=IF(M7="","",COUNTIFS(A$3:A$860,L$2,B$3:B$860,K7,D$3:D$860,M$2,E$3:E$860,">="&M7,E$3:E$860,"<="&N7))

Let me know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.