Excel Magic Trick 1483: LOOKUP Array Formula: 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 LOOKUP Function in an Array Formula.

Other 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

[LeilaGharani]

Best way to start my morning :) watching your video with a cup of coffee! Love how you've emphasized all the advantages of LOOKUP here - it really is an amazing function that I myself keep forgetting about. Thank you for this series. I'm looking forward to the next ones.

[RobMichaels]

Thanks Mike. I couldn't resist trying the Index-Match formula instead =INDEX($C$11:$D$17, MATCH(2, 1/(($B$11:$B$17=D22)*($C$11:$C$17<=C22)), 1), 2). Of course it also requires the dates to be sorted and using CSE. Great video. I look forward to the next ones!

[carlosguerra]

Great sir! Thank u so much for explaining every detail though still I need to study it hard. I have the similar table in product table but my price column has 3 lookups which (0-2), (3-6), (7 or more) - refering to the quantity being ordered.

Furthermore, i have order table and order detail table. In order table i have only the general info like order date, customer, agent, shipper, total amount. In order detail table has orderdetail ID, order id (foreign key to order table), product, quantity, price (to be generated from product lookup table), and line total.

I am very grateful to watch your videos and excited to learn more from those.

Keep it up! More powers.

[Learn_More_Pro]

Super sir this is satish from learn more channel

[SyedMuzammilMahasanShahi]

Amazing Mike with an other EXCELlent video. Thanks Man!

[johnborg]

Thanks Mike :) Amazing. This is Great Stuff!!!!

[MalinaC]

Wow, I've never used LOOKUP before, but now it's time to try ;)

[ennykraft]

Very interesting video! Since I have no problem with helper columns, I made one in A11 that's =B11&C1 etc, sorted it and then looked up the price with =VLOOKUP(D22&C22, $A$11:$D$17, 4).

[johnmartin]

I had it in my head that the lookup function had been superseded by the vlookup predominantly, and to a lesser scale by the hlookup. Now I have to reevaluate my thoughts Mike. Feeling confused, but only a bit. Top excel trick, by the master excel teach.

[mohamedchakroun]

Very Smart Mike I am falling behind cause of work Mike I am struggling to keep up the pace :-)

[林有鼠]

Hello Mike, I enjoyed your videos thoroughly. Thank your very much for sharing such valuable material with us.
But there is one thing I don't understand and would like to ask you this question. In this formula =LOOKUP(2, 1/(($B$11:$B$17=D22)*($C$11:$C$17<=C22)), $D$11:$D$17), what does the lookup value "2" do? I experimented with cell reference, 0, negative numbers, they all returned error value. And then I experimented with numbers 1 or any number 3, or 50 or 1000, they all returned the correct value. I don't know why the formula behaving like that. Could you please shed some light on this? Thanking you in advance.

[SANDYRUPA]

I have one question which I want to send you can please give me your mail id or other id where I can upload or send my question excel sheet please helpme