Looking up value based on multiple criteria over large dataset

[elevate_yourself]

Hi All,

I am hoping someone can help me figure out how to pull back a value based on multiple for a large data set that I can repeat in the spreadsheet thousands of times without crashing excel. The problem I have relates to a data set like the following:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FROM_DATE[/TD]
[TD]THRU_DATE[/TD]
[TD]ACCOUNT[/TD]
[TD]PRICE[/TD]
[TD]QUANTITY[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]04/01/2015[/TD]
[TD]04/30/2015[/TD]
[TD]1001[/TD]
[TD]10.50[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]05/01/2015[/TD]
[TD]06/30/2015[/TD]
[TD]1001[/TD]
[TD]11.00[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]07/01/2015[/TD]
[TD]12/31/2020[/TD]
[TD]1001[/TD]
[TD]7.50[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]04/06/2015[/TD]
[TD]04/07/2015[/TD]
[TD]2001[/TD]
[TD]4.50[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]04/08/2015[/TD]
[TD]04/10/2015[/TD]
[TD]2001[/TD]
[TD]9.00[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]04/11/2015[/TD]
[TD]04/20/2015[/TD]
[TD]2001[/TD]
[TD]8.75[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]04/01/2015[/TD]
[TD]04/30/2015[/TD]
[TD]3001[/TD]
[TD]3.25[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]05/01/2015[/TD]
[TD]06/30/2015[/TD]
[TD]3001[/TD]
[TD]5.00[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]07/01/2015[/TD]
[TD]12/31/2015[/TD]
[TD]3001[/TD]
[TD]12.00[/TD]
[TD]1000[/TD]
[/TR]
</tbody>[/TABLE]


What I would like the solution to do is be able to pull back the correct price and quantity for a reference data set that is configured like:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ACCOUNT[/TD]
[TD]MONTH[/TD]
[TD]YEAR[/TD]
[TD]DAY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1001[/TD]
[TD]OCT[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]07[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]15[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3001[/TD]
[TD]JUN[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]


So ideally the results in column E should be:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ACCOUNT[/TD]
[TD]MONTH[/TD]
[TD]YEAR[/TD]
[TD]DAY[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]10.50[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1001[/TD]
[TD]OCT[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]7.50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]07[/TD]
[TD]4.50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2001[/TD]
[TD]APR[/TD]
[TD]2015[/TD]
[TD]15[/TD]
[TD]8.75[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]3001[/TD]
[TD]JUN[/TD]
[TD]2015[/TD]
[TD]-[/TD]
[TD]5.00[/TD]
[/TR]
</tbody>[/TABLE]

**Its important to note that the full data set has ~100,000 rows so indexing of arrays and other expensive excel functions will not work because the formula will be repeated so many times.**

Any help would be greatly appreciated!

Thanks!

 

[ndsutherland]

SUMIFS would probably work for you assuming you don't have overlapping date ranges for the same account. Something like:

=SUMIFS(PriceRange,AccountRange,A1,From_DateRange,"<="&DATE(C2,B2,D2),Thru_DateRange,">="&DATE(C2,B2,D2))

 

[mperrah]

sumproduct works really well for such a project
use dynamic named ranges for the source and make a sheet for summary with datavalidation lists,
then output is the sumproduct using account number, start stop dates.

Q. why are some dates omitted from the results?

Q. do you pick the result data you want (ie which dates to display the range from)

Q. why are multiple dates in some months shown but some dates/months omitted?

 

[elevate_yourself]

Thanks ndsutherland! I had no idea you could combine the cells references inside the criteria of a sumif with another formula. It worked fantastic!

And thanks to mperrah as well I appreciate the assistance!