Multiple Criteria in Lookups with one being a date field

kiteman22 · Mar 16, 2010

I am currently trying to solve a problem where I am comparing two sheets, one with timesheets and the other with rates of pay. What I need to do is create a formula which looks at the person , the date and returns the rate of pay relating to that date, i.e. an exact match on the name but not on the date.

A B C D
Time sheets
Name Date rate hours
Fred 14/02/2010 lookup 40.00
Fred 14/03/2010 lookup 40.00
John 14/02/2010 lookup 40.00
John 14/03/2010 lookup 40.00

A B C D
Rates Change Date New Rate
Fred 01/01/2010 25.00
Fred 01/03/2010 35.00
John 01/01/2010 25.00
John 01/03/2010 40.00

Hope the above clarifies the problem

Kiteman22

sunnyland · Apr 14, 2010

Hello,
I think I found what you are looking for:
select Table 2 and paste in a worksheet to see if it gives you the wanted result.
I put all data on one sheet as it is easier to show on the forum.
The formula is :

=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A2)*($H$2:$H$7<=B2)*$H$2:$H$7)),--($G$2:$G$7=A2),$I$2:$I$7)

<head><style type='text/css'>p.mine{text-align: left;font-size: 10px;} em.code{background-color: #FFFFCC; color: #000066;font: normal;display: inline-block; width: 100%;}em.mineother{width: 80%;color: #000033; }table.mine{border-collapse:collapse;}tr.mine,td.mine {padding: 2px 2px 2px 2px; border: thin solid #9999CC;}td.minearray {background-color: #B7FFDC; padding: 2px 2px 2px 2px; border: thick solid #9999CC;}em.mine {font-size: 10px;width: 100%;background-color: #9F0000;color: White; }</style></head><table class='mine'><caption>Table 1. Shows a sample of datas. The worksheet is called Sheet1 </caption><tr><td class='mine'>Name</td><td class='mine'>Date</td><td class='mine'>rate</td><td class='mine'>hours</td><td class='mine'>Pay</td><td class='mine'> </td><td class='mine'>Rates</td><td class='mine'>Change Date</td><td class='mine'>New Rate</td></tr><tr><td class='mine'>Fred</td><td class='mine'>14/02/2010</td><td class='mine'>25</td><td class='mine'>40</td><td class='mine'> $ 1,000 </td><td class='mine'> </td><td class='mine'>Fred</td><td class='mine'>1/01/2010</td><td class='mine'>25</td></tr><tr><td class='mine'>Jess</td><td class='mine'>14/02/2010</td><td class='mine'>55</td><td class='mine'>40</td><td class='mine'> $ 2,200 </td><td class='mine'> </td><td class='mine'>Fred</td><td class='mine'>1/03/2010</td><td class='mine'>35</td></tr><tr><td class='mine'>John</td><td class='mine'>14/02/2010</td><td class='mine'>25</td><td class='mine'>40</td><td class='mine'> $ 1,000 </td><td class='mine'> </td><td class='mine'>John</td><td class='mine'>1/01/2010</td><td class='mine'>25</td></tr><tr><td class='mine'>Fred</td><td class='mine'>2/03/2010</td><td class='mine'>35</td><td class='mine'>40</td><td class='mine'> $ 1,400 </td><td class='mine'> </td><td class='mine'>John</td><td class='mine'>1/03/2010</td><td class='mine'>40</td></tr><tr><td class='mine'>Jess</td><td class='mine'>14/03/2010</td><td class='mine'>55</td><td class='mine'>40</td><td class='mine'> $ 2,200 </td><td class='mine'> </td><td class='mine'>Jess</td><td class='mine'>1/01/2010</td><td class='mine'>55</td></tr><tr><td class='mine'>John</td><td class='mine'>14/03/2010</td><td class='mine'>40</td><td class='mine'>40</td><td class='mine'> $ 1,600 </td><td class='mine'> </td><td class='mine'>Jess</td><td class='mine'>1/05/2010</td><td class='mine'>60</td></tr><tr><td class='mine'>Jess</td><td class='mine'>15/05/2010</td><td class='mine'>60</td><td class='mine'>40</td><td class='mine'> $ 2,400 </td><td class='mine'> </td><td class='mine'> </td><td class='mine'> </td><td class='mine'> </td></tr></table> <table class='mine'><caption>Table 2. Shows same data as Table 1. but with formulas<tr class='mine'><td class='mine'>Name</td><td class='mine'>Date</td><td class='mine'>rate</td><td class='mine'>hours</td><td class='mine'>Pay</td><td class='mine'></td><td class='mine'>Rates</td><td class='mine'>Change Date</td><td class='mine'>New Rate</td></tr><tr class='mine'><td class='mine'>Fred</td><td class='mine'>14/02/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A2)*($H$2:$H$7<=B2)*$H$2:$H$7)),--($G$2:$G$7=A2),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D2*C2</td><td class='mine'></td><td class='mine'>Fred</td><td class='mine'>1/01/2010</td><td class='mine'>25</td></tr><tr class='mine'><td class='mine'>Jess</td><td class='mine'>14/02/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A3)*($H$2:$H$7<=B3)*$H$2:$H$7)),--($G$2:$G$7=A3),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D3*C3</td><td class='mine'></td><td class='mine'>Fred</td><td class='mine'>1/03/2010</td><td class='mine'>35</td></tr><tr class='mine'><td class='mine'>John</td><td class='mine'>14/02/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A4)*($H$2:$H$7<=B4)*$H$2:$H$7)),--($G$2:$G$7=A4),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D4*C4</td><td class='mine'></td><td class='mine'>John</td><td class='mine'>1/01/2010</td><td class='mine'>25</td></tr><tr class='mine'><td class='mine'>Fred</td><td class='mine'>2/03/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A5)*($H$2:$H$7<=B5)*$H$2:$H$7)),--($G$2:$G$7=A5),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D5*C5</td><td class='mine'></td><td class='mine'>John</td><td class='mine'>1/03/2010</td><td class='mine'>40</td></tr><tr class='mine'><td class='mine'>Jess</td><td class='mine'>14/03/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A6)*($H$2:$H$7<=B6)*$H$2:$H$7)),--($G$2:$G$7=A6),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D6*C6</td><td class='mine'></td><td class='mine'>Jess</td><td class='mine'>1/01/2010</td><td class='mine'>55</td></tr><tr class='mine'><td class='mine'>John</td><td class='mine'>14/03/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A7)*($H$2:$H$7<=B7)*$H$2:$H$7)),--($G$2:$G$7=A7),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D7*C7</td><td class='mine'></td><td class='mine'>Jess</td><td class='mine'>1/05/2010</td><td class='mine'>60</td></tr><tr class='mine'><td class='mine'>Jess</td><td class='mine'>15/05/2010</td><td class='mine' >=SUMPRODUCT(--(($H$2:$H$7)=MAX(($G$2:$G$7=A8)*($H$2:$H$7<=B8)*$H$2:$H$7)),--($G$2:$G$7=A8),$I$2:$I$7)</td><td class='mine'>40</td><td class='mine' >=D8*C8</td><td class='mine'></td><td class='mine'></td><td class='mine'></td><td class='mine'></td></tr></table>

Multiple Criteria in Lookups with one being a date field

kiteman22

New Member

sunnyland

Well-known Member

Similar threads

Share this page

Multiple Criteria in Lookups with one being a date field

kiteman22

New Member

sunnyland

Well-known Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock