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

Excel Facts

sunnyland

Well-known Member

Similar threads

Forum statistics

Share this page

Multiple Criteria in Lookups with one being a date field

kiteman22

New Member

Excel Facts

sunnyland

Well-known Member

Similar threads

Forum statistics

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