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

[dawnmau]

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
adjust as needed.

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

 

[jorismoerings]

Hi,

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

Excel 2016 (Windows) 64 bit
	A	B	C	D	E
1	TS Date	Name ID	Total Labor	Value Hrly	Formula
2	27-2-2016	BP	1518,75	Need formula Results = 37,5	37,50
3	27-2-2016	JJ	1642,07	25,80	61,50
4	19-3-2016	DT	1337,59	27,87	27,87
5	26-3-2016	TW	965,95	22,21	22,21
Calculated Value vs Hrly

Cell Formulas
Range	Formula
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
	A	B	C	D
1	Name ID	Effective Date	End Date	Value
2	BP	6-5-2016	31-12-2078	3,000,00
3	BP	18-1-2016	5-5-2016	3,000,00
4	CF	22-2-2016	31-12-2078	1,903,85
5	DT	7-2-2016	31-12-2078	2,229,32
6	DT	23-12-2015	6-2-2016	2,166,50
7	JJ	1-4-2016	31-12-2078	2,855,77
8	JJ	27-12-2015	31-3-2016	2,855,77
9	JJ	8-2-2015	26-12-2016	2,064,00
10	TW	27-11-2016	31-12-2078	1,846,15
11	TW	7-2-2016	26-11-2016	1,776,47
12	TW	1-11-2015	6-2-2016	1,726,40
Value History

 

[dawnmau]

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

 

[dawnmau]

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

 

[dawnmau]

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]

What do you mean with " needs to work with text values as well . Could you explain what part needs to...

 

[dawnmau]

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