Lookup multiple values within multiple date ranges

[excelhelper69]

Hi,

Is there an automated way to look up a specific value's amount given that it needs to first identify a specific value and then the specific date range given?

Sample data - Identify the price after inputting Value 2 and 11/15/2017

Result should provide - $47.01

Value	Valid From	Valid To	Amount
Value 1	11/1/2017	11/25/2017	$47.01
Value 1	11/26/2017	12/31/2017	$56.40
Value 1	1/1/2018	1/31/2018	$54.05
Value 2	7/1/2017	10/31/2017	$56.40
Value 2	11/1/2017	11/25/2017	$47.01
Value 2	1/1/2018	1/31/2018	$54.05
Value 3	7/1/2017	10/31/2017	$56.40
Value 3	11/1/2017	11/25/2017	$47.01
Value 3	1/1/2018	1/31/2018	$54.05

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Thank you for any help provided!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Value​	Valid From​	Valid To​	Amount​		Value​	Date​	Result​
2​	Value 1​	11/01/2017​	11/25/2017​	47,01​		Value 2​	11/15/2017​	47,01​
3​	Value 1​	11/26/2017​	12/31/2017​	56,4​
4​	Value 1​	01/01/2018​	01/31/2018​	54,05​
5​	Value 2​	07/01/2017​	10/31/2017​	56,4​
6​	Value 2​	11/01/2017​	11/25/2017​	47,01​
7​	Value 2​	01/01/2018​	01/31/2018​	54,05​
8​	Value 3​	07/01/2017​	10/31/2017​	56,4​
9​	Value 3​	11/01/2017​	11/25/2017​	47,01​
10​	Value 3​	01/01/2018​	01/31/2018​	54,05​
11​

Formula in H2
=SUMIFS(D:D,A:A,F2,B:B,"<="&G2,C:C,">="&G2)

M.

 

[oldbrewer]

Sample data - Identify the price after inputting Value 2 and 11/15/2017

Result should provide - $47.01

Value

Valid From

Valid To

Amount

col H

Value 1

11/01/2017

11/25/2017

1

Value 1

11/26/2017

12/31/2017

2

Value 1

01/01/2018

1/31/2018

3

Value 2

Value 2

07/01/2017

10/31/2017

4

25/11/2017

Value 2

11/01/2017

25/11/2017

5

5

row 11

Value 2

01/01/2018

1/31/2018

6

Value 3

07/01/2017

10/31/2017

7

Value 3

11/01/2017

11/25/2017

8

Value 3

01/01/2018

1/31/2018

9

there was no 11/15/2017 AND

some of your dates are text and your dollar amounts were text

this formula gave 5 in H11

=SUMPRODUCT(($A$7:$A$15=$H$9)*($C$7:$C$15=$H$10)*($D$7:$D$15))

<colgroup><col><col><col><col span="4"><col><col span="5"></colgroup><tbody>
</tbody>

 

[excelhelper69]

Welcome to Mr Excel forum

Try

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Value​	Valid From​	Valid To​	Amount​		Value​	Date​	Result​
2​	Value 1​	11/01/2017​	11/25/2017​	47,01​		Value 2​	11/15/2017​	47,01​
3​	Value 1​	11/26/2017​	12/31/2017​	56,4​
4​	Value 1​	01/01/2018​	01/31/2018​	54,05​
5​	Value 2​	07/01/2017​	10/31/2017​	56,4​
6​	Value 2​	11/01/2017​	11/25/2017​	47,01​
7​	Value 2​	01/01/2018​	01/31/2018​	54,05​
8​	Value 3​	07/01/2017​	10/31/2017​	56,4​
9​	Value 3​	11/01/2017​	11/25/2017​	47,01​
10​	Value 3​	01/01/2018​	01/31/2018​	54,05​
11​

<tbody>
</tbody>

Formula in H2
=SUMIFS(D:D,A:A,F2,B:B,"<="&G2,C:C,">="&G2)

M.

Works perfect! Thank You!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.