Wanting to Look up data in between two dates

[jsymanowski]

Everyone,

I'm looking for a way to find data in a table based off of a date range (two user inputted dates). I have tried multiple ways (vlookup and index-match) but have been unsuccessful in my attempts. I was hoping to get some advice on here.

This is what I'm trying to do...

I have a table as listed below with the following data. This table is in the same workbook but in a different sheet.

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Name[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Identifier[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 69"]
<tbody>[TR]
[TD="class: xl65, width: 69"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 141"]
<tbody>[TR]
[TD="class: xl65, width: 141"]Water Production[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl65, width: 110"]Oil Production[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][FONT=Calibri, sans-serif]Ashton
[/FONT][/TD]
[TD]1000[/TD]
[TD]1/12/17[/TD]
[TD]10[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]2000[/TD]
[TD]1/12/17[/TD]
[TD]150[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]3000[/TD]
[TD]1/12/17[/TD]
[TD]21[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]2000[/TD]
[TD]2/21/17[/TD]
[TD]150[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Ashton[/TD]
[TD]1000[/TD]
[TD]2/12/17[/TD]
[TD]15[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Alex[/TD]
[TD]3000[/TD]
[TD]2/27/17[/TD]
[TD]20[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to search the name, by its identifier, to find the water production in between two dates. So...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Beginning Date[/TD]
[TD]Ending Date[/TD]
[TD]Identifier[/TD]
[TD]Water Production[/TD]
[/TR]
[TR]
[TD]2/1/17[/TD]
[TD]3/1/17[/TD]
[TD]2000[/TD]
[TD]*I need this cell to result in 150[/TD]
[/TR]
</tbody>[/TABLE]


Please let me know if anyone can help me out.
Thanks!

 

[FDibbins]

If there will only ever be 1 entry (or you want to sum all matches), try this...
=SUMIFS($D$2:$D$7,$B$2:$B$7,$C$12,$C$2:$C$7,">="&A12,$C$2:$C$7,"<="&B12)

 

[inactiveuserps07]

Could you provide the actual user-input cell addresses you're using, as well as the range where the table is found? It'll make it easier to write the formula.

 

[FDibbins]

Eric, very valid question

Based on the sample provided, I started it in column A...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Identifier[/td][td]Date[/td][td]Water Production[/td][td]Oil Production[/td][/tr]

[tr][td]

2​

[/td][td]Ashton[/td][td]1000[/td][td]1/12/2017[/td][td]10[/td][td]2[/td][/tr]

[tr][td]

3​

[/td][td]Fred[/td][td]2000[/td][td]1/12/2017[/td][td]150[/td][td]21[/td][/tr]

[tr][td]

4​

[/td][td]Alex[/td][td]3000[/td][td]1/12/2017[/td][td]21[/td][td]5[/td][/tr]

[tr][td]

5​

[/td][td]Fred[/td][td]2000[/td][td]2/21/2017[/td][td]150[/td][td]23[/td][/tr]

[tr][td]

6​

[/td][td]Ashton[/td][td]1000[/td][td]2/12/2017[/td][td]15[/td][td]5[/td][/tr]

[tr][td]

7​

[/td][td]Alex[/td][td]3000[/td][td]2/27/2017[/td][td]20[/td][td]5[/td][/tr]
[/table]

 

[inactiveuserps07]

Hi, Ford. And into which cells are you entering your date range beginning and ending, and the identifier you're checking?

 

[inactiveuserps07]

Actually, Ford, I'll just give you a "for instance" with the entry cells. Let's say that:

H2=Begin Date
H3=End Date
H4=ID

Then the formula would be as follows:

=SUMIFS(D2:D7,C2:C7,">="&H2,C2:C7,"<="&H3,B2:B7,H4)

 

[FDibbins]

Hi, Ford. And into which cells are you entering your date range beginning and ending, and the identifier you're checking?

[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td]Name[/td][td]Identifier[/td][td]Date[/td][td]Water Production[/td][td]Oil Production[/td][/tr]

[tr][td]

2​

[/td][td]Ashton[/td][td]

1000​

[/td][td]

1/12/2017​

[/td][td]

10​

[/td][td]

2​

[/td][/tr]

[tr][td]

3​

[/td][td]Fred[/td][td]

2000​

[/td][td]

1/12/2017​

[/td][td]

150​

[/td][td]

21​

[/td][/tr]

[tr][td]

4​

[/td][td]Alex[/td][td]

3000​

[/td][td]

1/12/2017​

[/td][td]

21​

[/td][td]

5​

[/td][/tr]

[tr][td]

5​

[/td][td]Fred[/td][td]

2000​

[/td][td]

2/21/2017​

[/td][td]

150​

[/td][td]

23​

[/td][/tr]

[tr][td]

6​

[/td][td]Ashton[/td][td]

1000​

[/td][td]

2/12/2017​

[/td][td]

15​

[/td][td]

5​

[/td][/tr]

[tr][td]

7​

[/td][td]Alex[/td][td]

3000​

[/td][td]

2/27/2017​

[/td][td]

20​

[/td][td]

5​

[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Beginning Date[/td][td]Ending Date[/td][td]Identifier[/td][td]Water Production[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]

2/1/2017​

[/td][td]

3/1/2017​

[/td][td]

2000​

[/td][td]

150​

[/td][td][/td][/tr]
[/table]
I used D12
D12=SUMIFS($D$2:$D$7,$B$2:$B$7,$C$12,$C$2:$C$7,">="&A12,$C$2:$C$7,"<="&B12)

 

[_szym]

Here is a nice way of solving the problem at hand:

=SUMPRODUCT(--(C2:C7>=A12),--(C2:C7<=B12),--(B2:B7=C12),D2:D7)