vLookup using multiple criteria including date range

[Umesh]

I have a following scenario,
CustProdSt DateEnd DatePrice
Cust1Prod11/1/201212/31/2012 10.00
Cust1Prod11/1/20136/30/2013 11.00
Cust1Prod17/1/201312/31/2105 12.00
Cust1Prod21/1/201412/31/2014 20.00
Cust1Prod21/1/201512/31/2105 21.00

Using Cust1, Prod1 and Date as parameter, I want to grab the price.

I apologize if there is a solution already out there for this - however I already looked at vLookup using multiple criteria and vLookup using date range. But in this case, I have a combination of both.

Any help on this would be greatly appreciated.

 

[Luke M]

Hello, and welcome to the forum!

Could use a SUMIFS actually, since the result should be a single number. Something like:
=SUMIFS(E2:E100,A2:A100,CustName,B2:B100,ProdName,C2:C100,"<="&SomeDate,D2:D100,">="&SomeDate)

With appropriate cell ranges filled in for the names.

 

[Umesh]

Luke,
This was excellent! It worked. Thank you for the quick and (most importantly) best response
Great job Chandoo for collaborating.
Regards.

 

[Umesh]

2 issues:

1) The solution fails if the list is not sorted.
2) Also, if it finds multiple rows for the given criteria, it adds the result (due to SumIFs).

Is there a way to fix it?

 

[prasaddn]

Hi,

Pls post a sample file with sample data.

Regards,
Prasad DN

 

[Umesh]

Hello Prasad,
Here is the sample file. I hope it helps.
Regards,
-Umesh

 

[prasaddn]

Hi,

use below formula as CSE:
=MIN(IF((A2:A16=A19)*(B2:B16=A20)*(C2:C16<=A21),E2:E16,""))

regards,
Prasad DN

 

[Umesh]

Hi Prasad, I get an '#VALUE' error when I plug in the formula. Am I doing something wrong.

 

[prasaddn]

I guess its because you are not entering the formula as CSE.

CSE, means you will type or paste the above formula in a cell and instead of you hit enter or return key, you will hit Cntrl+Shift+Enter key together.

Regards,
Prasad DN
PS: if you enter as CSE, you will see the formula gets { and } automatically added.

 

[Umesh]

Thank you for the clarification - yes it does work now and gives the correct result.
However, I ran into another issue, which I should have mentioned earlier (apologize for missing out). There are about 15,000 rows to be calculated and there is a Customer/Product Price file in which it has to reference, which has about 100,000 rows in it. So, it takes about 30-45 minutes to generate the results.
Is there a way to make it faster?