Multiple Criteria Look Up Help

[Mr.Big Head]

I posted previously on here with no luck. I have a large data set that I am trying to bounce a small data set against using a look up. I thought that perhaps INDEX or MATCH would work, but so far I have yet to find a good result. Below is a sample of my data set:

Column A: Number
Column B: Price
Column C: Beginning Date
Column D: Ending Date

In column A I could have several duplicates of one number with varying prices and beginning and ending dates. Those dates do not overlap. In my smaller data set, I want to look up a number, and return the correct price based on the date.

For Example

Sample Data: Number 123, Date of Service 5/5/2012


In my master data I have multiple rows of number 123, each with a unique date range and unique dollar amount. My goal is to create a formula that would return the correct price based on the Date of service in the sample data.

Does anybody know if this is possible? Any help is appreciated.

Thanks!

 

[pplstuff]

Make a column in each data set that is "=a1&"|"&C1" then use vlookup to return the second column.

 

[PaddyD]

If you can guarantee that there are no overlapping dates, then (untested):

=sumproduct(--(a1:a100=number),--(c1:c100< =date),--(d1:d100>=date),b1:b100)

 

[Mr.Big Head]

@PaddyD This formula works great. However, I am running into a problem. I get the error #VALUE!, and it appears that it is because the master data set is too large (300,000+ rows) It works until right around row 100,000. Do you have any other ideas that does not involve splitting my data set?

Thanks

 

[pplstuff]

If your data are large, did you let it calculate all the way? Just a thought.

 

[Mr.Big Head]

Yeah, It had ample time to calculate. Also there was no calculating status progress bar in the lower right of my excel window.

 

[PaddyD]

Hhmm...

Only documented condition for sumproduct returning #value is:

"The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value."

...but then I'm only on 2003. A big dataset should just mean it takes ages to calculate.

Another approach:

=INDEX(B1:B300000,SMALL(IF(A1:A300000=number,IF(C1:C300000< =date,IF(D1:D300000>=date,ROW(B1:B300000),ROW(B300000)+1))),1))

..entered with control + shift + enter, not just enter, which will probably take weeks to calculate

 

[Mr.Big Head]

I figured it out...There was a value in my data set of #VALUE!, which was the reason for the return of #VALUE!...doh

 

[PaddyD]

Glad you've got it sorted.

 

[Mr.Big Head]

Thanks for the help!