Formula not matching to correct date in price history

[Ochimus]

Can't work out formula to find the relevant price for a given date.

Col A on attached lists range of products. Col B lists the dates of price raises. Col C lists the relevant price at each increase.

Formula in Col G

{=INDEX($C$1:$C$31, MATCH(E3&F3, $A$1:$A$31&$B$1:$B$31,0))}

should find the price in Col C for the product in Col A matching E3, at the date in Col B that covers the date in F3, but setting it as "exact" match generates an error between dates, and setting it as more or less than generates only the orignal value.

Col J shows how it should work..

Any pointers or solutions accepted gratefully as ever.

Ochimus

[Khalidngo]

Hi Ochimus,

Your formula is working fine as it should.
Error values are because there is no matching values, for example there is no 15-Dec-2015 for Product 1.

Similar result can be achieved with simple regular SUMIFS:
=SUMIFS($C$2:$C$31,$A$2:$A$31,E3,$B$2:$B$31,F3)

Regards,

[Ochimus]

Khalidngo,

Appreciate the quick response,m but your approach does not work either.

If you apply the SumIfs to Row 3, it finds the pricxe at £1.00, as did the Index Match,

But if you copy it to row 4, the value for a sale on 15 Dec should be the price that started on 1 December, because the price does not go up to £1.02 until 1 Jan.

Your "Sumifs" formula results in a Zero, not £1.00

Ochimus

[JohnTopley]

Sort table by Product/Date in ascending order

then try

=INDEX($C$1:$C$31,MATCH($F3,OFFSET($B$1,MATCH($E3,$A$2:$A$31,0), 0,COUNTIF($A$2:$A$31,$E3),1),1)+MATCH($E3,$A$2:$A$31,0))

[Ochimus]

John,

Interesting approach, although was hoping to get it to work where the End User can add any product, effective date and new price on the next free row of the sheet.

(To simplify the issue, the sample gives every product in the range an increase on the same day each month, and keeps them in the same "running order".)

Ochimus

[Phuocam]

Or try this ...

=LOOKUP(10^10,$C$1:$C$31/($A$1:$A$31=$E3)/($B$1:$B$31<=$F3))

[JYTS]

Ochimus, as long as John's response solves your problem, here it is edited to allow a full million rows of data.

=INDEX(C:C,MATCH($F3,OFFSET($B$1,MATCH($E3,A:A,0), 0,COUNTIF(A:A,$E3),1),1)+MATCH($E3,A:A,0))

(1) I cringe at posting a formula with whole column references in it, but Excel has gotten better at handling them.
(2) Don't use this if recalculation performance is a serious consideration. Both whole column refs and the use of OFFSET may slow things down.

[JohnTopley]

Phuocam has given you the solution: I wasn't too pleased with my own and knew there must be simpler way.

[Ochimus]

Thanks everyone,

Phuocam's solution works perfectly, but have not seen that structure before.

Can follow the column and cell references, but what is the "10^10" lookup value?

Ochimus

[JohnTopley]

10^10 is just a large number which ensures that no value will exceed this amount so LOOKUP finds the first value which less than or equal to 10^10.

[Ochimus]

John,

Now clear.

Must admit to never being an "aficionado" of LookUps, preferring Index/Match as the approach, but it came up trumps on this issue.

Thanks again to everyone for the prompt help, and can now mark this as "Solved"

Ochimus

[Ochimus]

Sorry - message duplicated for some reason! Hopefully someone further up the Food Chain can remove this altogether?