Multiple criteria (finding closest)

Hi. I hope you can help.

I would like to determine the price that is valid on a given date:

Product Client Date Price
Product A Client 1 1/1/13 10.00
Product A Client 1 1/3/13 10.50
Product A Client 1 1/6/13 11.00
Product A Client 2 1/1/13 10.00
Product B
Product B
Product B

In this case, let us say that the Product is in Column A, Client in Column B and so on. I want to built another table which looks at the price by date:

So it might be:


27/2/13 10.00
28/2/13 10.00
1/3/13/ 10.50

and so on. Could someone help me with the formula such that it isn't too slow and there will be a lot of dates and products.

Thanks a lot.
James

What is your expected output when there's more than one of the same date in your data source?

There shouldn't be more than one combination of product of product, customer and date. If there is no date available for that period is should return an error if possible. so in this example, is there is a date for 2012 then there would be no valid date.

Thanks for your help.

Your second table doesn't have product or customer identified as a criteria.

If you simply listed dates in your 2nd table, and looked up only matches for dates you would find the first and fourth lines from the data source that both match.

They are for different clients, but theres doesn't appear to be a list of clients in your second table.

If your second table listed a client, a product, and a date, a SUMPRODUCT formula could be utilized to retrieve that item's price without going to find it manually. Is that what you need?

=SUMPRODUCT((RANGE1=CRITERIA1)*(RANGE2=CRITERIA2)*(RANGE3=CRITERIA3)*(RANGE4))

=SUMPRODUCT((A1:A10=PRODUCT1)*(B1:B10=CLIENT1)*(C1:C10=a date)*(D1:D10)) would give you the price

Hi, sorry I should have been clearer. The second table is looking up the price that is effective on those dates for Product A and Client 1.

So on the 27th Feb and 28th feb 10.00 is the price but on the 1st mar the price has changed to 10.50. I want the formula to find the price which is effect as the pricing only changes every so often. The date in the second table may not be explicitly in the first table.

Does that make sense?

It does. You would use an approximate match instead of exact which will round to the most recent date.

I've attached a sample of how it could work.

=INDEX(prices,MATCH(date,IF(products=specific product,IF(clients=specific client,dates))))

Note the formula is entered as an array, using Ctrl+Shift+Enter instead of exiting the cell just using Enter.

In my sample I used this, where I17 had a specific date, G17 was ProductA, and H17 was Client1.

=INDEX(D3:D10,MATCH(I17,IF(A3:A10=G17,IF(B3:B10=H17,C3:C10))))


three criteria match.xlsx

Thanks that works really well. Really appreciate your help.

thanks its good for matching a value.