# Need formula to lookup based on TS Date & Name ID with TS Date Btwn Eff & End Date

#### dawnmau

##### New Member
Hi All,

I am in need of assistance as to what type of Formula I should use and the basic structure of that formula
CASE FACTS AND DETAILS
Lookup values Name ID and TS Date; go to Value History wksht, find line where Name ID matches and TS Date falls between Effective Date and End Date and then enter the contents in the "Value" cell for that line and divide by 80

Calculated Value vs Hrly
 TS Date Name ID Total Labor Value Hrly 2/27/2016 BP 1518.75 Need formula Results = 37.5 2/27/2016 JJ 1642.07 25.8 3/19/2016 DT 1337.59 27.866 3/26/2016 TW 965.95 22.2058

<tbody>
</tbody>

Table where value resides
 Name ID Effective Date End Date Value BP 5/6/2016 12/31/2078 3000 BP 1/18/2016 5/5/2016 3000 CF 2/22/2016 12/31/2078 1903.85 DT 2/7/2016 12/31/2078 2229.32 DT 12/23/2015 2/6/2016 2166.5 JJ 4/1/2016 12/31/2078 2855.77 JJ 12/27/2015 3/31/2016 2855.77 JJ 2/8/2015 12/26/2016 2064 TW 11/27/2016 12/31/278 1846.15 TW 2/7/2016 11/26/2016 1776.47 TW 11/1/2015 2/6/2016 1726.4

<tbody>
</tbody>

Please let me know what other information is needed. I kept this basic but as long as I know the mechanics of the formula I can

The file is comprised of two worksheets; "Calculated Value vs Hrly" and the "Value History". I want to pull the information from
the "Value History" wksht and have it "deposited" in the appropriate column of the "Calculated Value vs Hrly" wksht

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### jorismoerings

##### Well-known Member
Hi,

take a look at this and see if this is what you're after:

Excel 2016 (Windows) 64 bit
ABCDE
1TS DateName IDTotal LaborValue HrlyFormula
227-2-2016BP1518,75Need formula Results = 37,537,50
327-2-2016JJ1642,0725,8061,50
419-3-2016DT1337,5927,8727,87
526-3-2016TW965,9522,2122,21
Calculated Value vs Hrly
Cell Formulas
RangeFormula
E2=SUMPRODUCT(('Value History'!\$A\$2:\$A\$12=B2)*('Value History'!\$B\$2:\$B\$12<=A2)*('Value History'!\$C\$2:\$C\$12>=A2)*('Value History'!\$D\$2:\$D\$12))/80

NB: I couldn't match your answer on the second row. That's caused because the value history table contains an overlap in periods; didn't know if that's just a typo or if you want the formula to elimante any overlap. I assumed it was just a typo.

Excel 2016 (Windows) 64 bit
ABCD
1Name IDEffective DateEnd DateValue
2BP6-5-201631-12-20783,000,00
3BP18-1-20165-5-20163,000,00
4CF22-2-201631-12-20781,903,85
5DT7-2-201631-12-20782,229,32
6DT23-12-20156-2-20162,166,50
7JJ1-4-201631-12-20782,855,77
8JJ27-12-201531-3-20162,855,77
9JJ8-2-201526-12-20162,064,00
10TW27-11-201631-12-20781,846,15
11TW7-2-201626-11-20161,776,47
12TW1-11-20156-2-20161,726,40
Value History

Last edited:

#### dawnmau

##### New Member
LOL! No that is a typo on my part! It should be 12-31-2078, not 31-3-2016.
Dawn

#### dawnmau

##### New Member
Thank you! Formula worked and I very much appreciate your help!

#### dawnmau

##### New Member
So... I was merrily applying this formula to my data file and realized I need something that will work with Text values as well. Any suggestions?

#### jorismoerings

##### Well-known Member
What do you mean with " needs to work with text values as well . Could you explain what part needs to...

#### dawnmau

##### New Member
When I apply the formula to columns that are "text" based values, it doesn't work. so I am not sure how to look up and bring in the contents of the cell when the "value" is text. The text column is not part of the sample I provided.
 TS Date Name ID Total Labor Value Hrly S/H 2/27/2016 BP 1518.75 Formula results Formula Results = S 3/26/2016 TW 965.95 21.58 H

<tbody>
</tbody>

 Name ID Effective Date End Date Value S/H BP 06/05/2016 12/31/2078 3000 S BP 1/18/2016 6/4/2016 2750 S TW 11/27/2016 12/31/2078 1846.15 S TW 12/22/2015 11/26/2016 1726.40 H

<tbody>
</tbody>

When I try the sumproduct formula, I get #VALUE! as the results.

Hope this illustration helps explain what I am trying to do.
Dawn

Replies
3
Views
250
Replies
1
Views
368
Replies
2
Views
682
Replies
5
Views
2K
Replies
5
Views
806

1,191,379
Messages
5,986,285
Members
440,016
Latest member
vasanrajeswaran

### 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.

### Which adblocker are you using?

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

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