Lookup multiple values within multiple date ranges

E

excelhelper69

New Member
Joined
Feb 26, 2018
Messages
2
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


[TABLE="width: 418"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Value[/TD]
[TD]Valid From[/TD]
[TD] Valid To[/TD]
[TD] Amount [/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]11/1/2017 [/TD]
[TD] 11/25/2017[/TD]
[TD] $47.01[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]11/26/2017 [/TD]
[TD]12/31/2017[/TD]
[TD] $56.40[/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]1/1/2018[/TD]
[TD] 1/31/2018[/TD]
[TD] $54.05[/TD]
[/TR]
[TR]
[TD]Value 2 [/TD]
[TD]7/1/2017[/TD]
[TD] 10/31/2017[/TD]
[TD] $56.40[/TD]
[/TR]
[TR]
[TD]Value 2 [/TD]
[TD]11/1/2017[/TD]
[TD]11/25/2017[/TD]
[TD] $47.01[/TD]
[/TR]
[TR]
[TD]Value 2 [/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[TD] $54.05[/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD]7/1/2017[/TD]
[TD]10/31/2017[/TD]
[TD] $56.40[/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD]11/1/2017[/TD]
[TD]11/25/2017[/TD]
[TD] $47.01[/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD]1/1/2018[/TD]
[TD]1/31/2018[/TD]
[TD] $54.05[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help provided!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Welcome to Mr Excel forum

Try


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Value​
[/td][td]
Valid From​
[/td][td]
Valid To​
[/td][td]
Amount​
[/td][td][/td][td]
Value​
[/td][td]
Date​
[/td][td]
Result​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
Value 1​
[/td][td]
11/01/2017​
[/td][td]
11/25/2017​
[/td][td]
47,01​
[/td][td][/td][td]
Value 2​
[/td][td]
11/15/2017​
[/td][td]
47,01​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Value 1​
[/td][td]
11/26/2017​
[/td][td]
12/31/2017​
[/td][td]
56,4​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Value 1​
[/td][td]
01/01/2018​
[/td][td]
01/31/2018​
[/td][td]
54,05​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
Value 2​
[/td][td]
07/01/2017​
[/td][td]
10/31/2017​
[/td][td]
56,4​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
Value 2​
[/td][td]
11/01/2017​
[/td][td]
11/25/2017​
[/td][td]
47,01​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
Value 2​
[/td][td]
01/01/2018​
[/td][td]
01/31/2018​
[/td][td]
54,05​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
Value 3​
[/td][td]
07/01/2017​
[/td][td]
10/31/2017​
[/td][td]
56,4​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Value 3​
[/td][td]
11/01/2017​
[/td][td]
11/25/2017​
[/td][td]
47,01​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
Value 3​
[/td][td]
01/01/2018​
[/td][td]
01/31/2018​
[/td][td]
54,05​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

M.
 
Upvote 0
[TABLE="width: 958"]
<colgroup><col><col><col><col span="4"><col><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 7"]Sample data - Identify the price after inputting Value 2 and 11/15/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Result should provide - $47.01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value
[/TD]
[TD]Valid From[/TD]
[TD]Valid To[/TD]
[TD]Amount[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]col H[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD="align: right"]11/01/2017[/TD]
[TD]11/25/2017[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD]11/26/2017[/TD]
[TD]12/31/2017[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 1[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD]1/31/2018[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Value 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD="align: right"]07/01/2017[/TD]
[TD]10/31/2017[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD="align: right"]11/01/2017[/TD]
[TD="align: right"]25/11/2017[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]row 11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 2[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD]1/31/2018[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD="align: right"]07/01/2017[/TD]
[TD]10/31/2017[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD="align: right"]11/01/2017[/TD]
[TD]11/25/2017[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Value 3[/TD]
[TD="align: right"]01/01/2018[/TD]
[TD]1/31/2018[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]there was no 11/15/2017 AND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]some of your dates are text and your dollar amounts were text[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]this formula gave 5 in H11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($A$7:$A$15=$H$9)*($C$7:$C$15=$H$10)*($D$7:$D$15))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Marcelo Branco said:
Welcome to Mr Excel forum

Try


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[TD="bgcolor: #DCE6F1"]
D​
[/TD]
[TD="bgcolor: #DCE6F1"]
E​
[/TD]
[TD="bgcolor: #DCE6F1"]
F​
[/TD]
[TD="bgcolor: #DCE6F1"]
G​
[/TD]
[TD="bgcolor: #DCE6F1"]
H​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Value​
[/TD]
[TD]
Valid From​
[/TD]
[TD]
Valid To​
[/TD]
[TD]
Amount​
[/TD]
[TD][/TD]
[TD]
Value​
[/TD]
[TD]
Date​
[/TD]
[TD]
Result​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Value 1​
[/TD]
[TD]
11/01/2017​
[/TD]
[TD]
11/25/2017​
[/TD]
[TD]
47,01​
[/TD]
[TD][/TD]
[TD]
Value 2​
[/TD]
[TD]
11/15/2017​
[/TD]
[TD]
47,01​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Value 1​
[/TD]
[TD]
11/26/2017​
[/TD]
[TD]
12/31/2017​
[/TD]
[TD]
56,4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Value 1​
[/TD]
[TD]
01/01/2018​
[/TD]
[TD]
01/31/2018​
[/TD]
[TD]
54,05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Value 2​
[/TD]
[TD]
07/01/2017​
[/TD]
[TD]
10/31/2017​
[/TD]
[TD]
56,4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Value 2​
[/TD]
[TD]
11/01/2017​
[/TD]
[TD]
11/25/2017​
[/TD]
[TD]
47,01​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Value 2​
[/TD]
[TD]
01/01/2018​
[/TD]
[TD]
01/31/2018​
[/TD]
[TD]
54,05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Value 3​
[/TD]
[TD]
07/01/2017​
[/TD]
[TD]
10/31/2017​
[/TD]
[TD]
56,4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Value 3​
[/TD]
[TD]
11/01/2017​
[/TD]
[TD]
11/25/2017​
[/TD]
[TD]
47,01​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Value 3​
[/TD]
[TD]
01/01/2018​
[/TD]
[TD]
01/31/2018​
[/TD]
[TD]
54,05​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

M.
Click to expand...


Works perfect! Thank You!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Jyggalag
  • Solved
Formula to calculate between dates
Replies
4
Views
647
Peter_SSs
Peter_SSs
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
499
etaf
etaf
M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
552
mrshuck
M
Kellie220
  • Question Question
VBA to Collapse Rows (3 part question)
Replies
1
Views
272
Artik
Artik
H
  • Solved
Delete all rows including cells in column "A" that are identical to the values in cells "D1", "E1","F1" and "A6".
2
Replies
10
Views
575
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top