• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

match or lookup with date and and other conditions

Y

yelkus

New Member
Hoping someone can help... much appreciated.


I have an "Orders sheet" which needs to get data from a "Price List sheet".

I need a lookup which will check for: the matching Product and the most recently updated price (relative to the order date). So for each order - the formula should find the price which was current at that date.

I have put in the results that the lookup should retrieve below.


Orders Sheet

Date, Product, Price

01/10/2011, Cucumber, ? (lookup value should be 10.50)

05/10/2011, Orange, ? (lookup value should be 5.00)

05/10/2011, Cucumber, ? (lookup value should be 11.50)


Price List Sheet

Date, Product, Price

01/10/2011, Cucumber, 10.50

06/10/2011, Cucumber, 12.50

02/10/2011, Orange, 5.00

02/10/2011, Cucumber, 11.50


Thanks for your help!
 
Hi ,


Try the following formula in cells C2 , C3 ,.... in your Orders worksheet.


=LARGE((Orders!A2>='Price List'!$A$2:$A$5)*('Price List'!$B$2:$B$5=Orders!B2)*('Price List'!$C$2:$C$5),1)


Narayan
 
Hi Narayan,

You are a genius - it looks like it works.

I'll let you know if I have any more issues with it.

Thanks so so much!
 
Hi Narayan,

It appears that the function is not working the way that I had hoped.

Is there a way that I can upload an excel so that you can take a look?

Thanks,

Yirmie
 
Hi ,


Sure , no problem. However , it is almost bedtime here , so I might not be able to get the solution till tomorrow.


Narayan
 
Hi,

Thanks very much for your help.


Here is the link:

https://docs.google.com/open?id=0B0YbL3NgjCBQNThjMWU1YWUtZDc1ZS00N2VjLWJkNTAtY2JlOWQ2MTU1M2Fl


Basically, the orders & delivery sheet is meant to bring in the most current price (from the price list sheet) at each date.


You can see that in the Price List sheet - there was a new price on 5/12/2011 for Tom > Beef Tomato. But in the Order & Delivery sheet - it still brings in the price from 29/11/2011.


Thanks again (and have a good sleep!!).
 
Hi ,


Can you check out the following link ? The revised formulae are in column J ; I have used 3 new range names , merely for convenience.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21141


Please note that clicking on the link will not do ; please copy the entire address and paste it in your browser.


Narayan
 
It looks great!

Thanks very very much.

I'll try putting into my spreadsheet and see how it goes.

Very much appreciated.
 
You must log in or register to reply here.
Back
Top