Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date

In this video learn how to Lookup the correct product price based on the latest Effective Date. The Lookup table has multiple listings of the product, each with a different effective date and price. In this video learn about how to use the SUMIFS & MAXIFS functions.

Related Videos:
Excel Magic Trick 1483: LOOKUP Array Formula: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1484: LOOKUP & Sorted Helper Column: Lookup Price Based on Effective Date
Excel Magic Trick 1485: SUMIFS & MAXIFS Functions: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1486: VLOOKUP, MAXIFS, & Key: Lookup Correct Price Based on Effective Date
Excel Magic Trick 1487: INDEX, MATCH & MAXIFS : Lookup Correct Price Based on Effective Date
Excel Magic Trick 1488: Vote For Favorite Formula to Lookup Correct Price Based on Effective Date

[BillSzysz]

I am glad to see EMT again.
A perfect solution as always!!
If you do not have MAXIFS and AGGREGATE then... you have an outdated excel. ;-))
But even then you can solve it.
=MOD(MAX(IF($B$11:$B$17=D22, IF($C$11:$C$17<=C22, $C$11:$C$17*1000+$D$11:$D$17))), 1000)
Of course, you can replace value 1000 (depending on the prices in lookup table) .
Thanks again Mike for your great videos!!!

[ismailismaili]

the best always Mr. Mike i really don't know what to say and how to thank you anymore but you are always the best for me

[DougHExcel]

Excel-lent again! Thanks for the MAXIFS example.

[MalinaC]

Wow... magic indeed! I can't imagine Excel without you! :)

[johnborg]

Thanks Mike :) Simply Beautiful!!! I love it when Bill Szysz comes with a different solution. Pity I don't speak Polish (:

[AL-ghxs]

Thank you for sharing! This is super helpful!

[NoShadowOfDoubt]

Great formula ...thank you.... no I love it!

[RobMichaels]

This isn't any better but it also works and does not use #DIV/0 to filter in the Aggregate function: =SUMIFS($D$11:$D$17, $C$11:$C$17, AGGREGATE(14, 4, $C$11:$C$17*($B$11:$B$17=D22)*($C$11:$C$17<=C22), 1), $B$11:$B$17, D22). Thanks for all the Excel fun !

[YitzhakMoniz]

Another great tutorial. Question is it possible to reverse this. By that I mean lookup the historical price. The oldest date recorded. I would really appreciate your feedback on this.

[RenePante]

Gracias. Excelente información. Saludos :)

[JUHILLAPSIWALA]

Sir
I have excel query that I want vlookup value total from 4 sheet in different Excel sheet

[simfinso]

sir can I ask question About NPV here ( I know it is not related)

[rakeshtomar]

Maxifs is not available in Excel 2007 😥