Max Look Up with Multiple Criteria

darian

New Member
Joined
Jun 6, 2014
Messages
10
I have a time sheet Table that I pull from Share point and it's linked. to fill the table I have 4 criteria that must be met .Any formula I use It gives me zero result and don't show me the actual Time in the time sheet. Can you help me?

Employee Name Sales Manager ClockType Date Time
Doe, JaneAmber, SmithClock In8/2/20161:2 PM
Doe, JaneAmber, SmithLunch In8/2/20161:3 PM
Doe, JaneAmber, SmithClock Out8/2/20161:22 PM

<tbody>
[TD="align: right"]8/2/16 13:02[/TD]

[TD="bgcolor: #DBE5F1"]Doe, Jane[/TD]
[TD="bgcolor: #DBE5F1"]Amber, Smith[/TD]
[TD="bgcolor: #DBE5F1"]Lunch Out[/TD]
[TD="bgcolor: #DBE5F1"]8/2/2016[/TD]
[TD="bgcolor: #DBE5F1"]1:2 PM[/TD]
[TD="bgcolor: #DBE5F1, align: right"]8/2/16 13:02[/TD]

[TD="align: right"]8/2/16 13:03[/TD]

[TD="bgcolor: #DBE5F1"]Doe, Jane[/TD]
[TD="bgcolor: #DBE5F1"]Amber, Smith[/TD]
[TD="bgcolor: #DBE5F1"]Clock Out[/TD]
[TD="bgcolor: #DBE5F1"]8/2/2016[/TD]
[TD="bgcolor: #DBE5F1"]1:3 PM[/TD]
[TD="bgcolor: #DBE5F1, align: right"]8/2/16 13:03[/TD]

[TD="align: right"] 8/2/16 13:22 [/TD]

[TD="bgcolor: #DBE5F1"]Doe, Jane[/TD]
[TD="bgcolor: #DBE5F1"]Amber, Smith[/TD]
[TD="bgcolor: #DBE5F1"]Clock In[/TD]
[TD="bgcolor: #DBE5F1"]8/3/2016[/TD]
[TD="bgcolor: #DBE5F1"]9:15 AM[/TD]
[TD="bgcolor: #DBE5F1, align: right"]8/3/16 9:15[/TD]

</tbody>

Now I need to fill my below table and I might need to use max because an employee may input their date twice :

1) Employee:
2) Sales Manager:
Doe, Jane
Doe, Jane
Doe, Jane

<tbody>
[TD="bgcolor: #FFFF00"]Doe, Jane[/TD]
[TD="colspan: 2, align: center"]Today's Date:[/TD]
[TD="align: center"]8/3/2016[/TD]

[TD="align: center"]Amber, Smith[/TD]
[TD="colspan: 2, align: center"]Week Startting:[/TD]
[TD="align: center"]8/1/2016[/TD]

[TD="bgcolor: #DBE5F1, colspan: 2"]Date[/TD]
[TD="bgcolor: #DBE5F1"]Employee[/TD]
[TD="bgcolor: #DBE5F1"] 4) Clock In[/TD]
[TD="bgcolor: #DBE5F1"]Lunch Out[/TD]
[TD="bgcolor: #DBE5F1"]Lunch In[/TD]
[TD="bgcolor: #DBE5F1"]Clock Out[/TD]
[TD="bgcolor: #DBE5F1"]Total Lunch[/TD]
[TD="bgcolor: #DBE5F1"]Unpaid Lunch[/TD]
[TD="bgcolor: #DBE5F1"]Total Paid Hrs[/TD]

[TD="bgcolor: #F2F2F2"]Monday[/TD]
[TD="bgcolor: #F2F2F2"] 3) 8/1/2016[/TD]

[TD="align: right"]0:00[/TD]
[TD="align: center"]#VALUE![/TD]

[TD="bgcolor: #F2F2F2"]Tuesday[/TD]
[TD="bgcolor: #F2F2F2"]8/2/2016[/TD]

[TD="align: right"]0:00[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]0:00[/TD]
[TD="align: center"]#VALUE![/TD]

[TD="bgcolor: #F2F2F2"]Wednesday[/TD]
[TD="bgcolor: #F2F2F2"]8/3/2016[/TD]

[TD="align: right"]0:00[/TD]
[TD="align: center"]#VALUE![/TD]

[TD="bgcolor: #F2F2F2"]...[/TD]
[TD="bgcolor: #F2F2F2"]...[/TD]

[TD="align: right"][/TD]
[TD="align: center"][/TD]

[TD="bgcolor: #EEECE1"]Total Hours[/TD]
[TD="bgcolor: #EEECE1, align: center"]#VALUE![/TD]

</tbody>
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,226,735
Messages
6,192,733
Members
453,752
Latest member
Austin2222

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top