Find Nearest Price

[mdma]

I have two worksheets, one which contains an item number and order date, and one which contains item number, start date and price. I am trying to lookup the price based on item number and order date and nearest price date.

For example,

Item 123

Prices 01/01 = 10
Price 01/02 = 20

If I enter order date 31/12 it should NOT return a price

Order date = 05/01 should return 10

Order date = 30/01 should return 10

01/02 = 20

etc

I have tried the INDEX / MATCH with MIN and ABS, but of course this finds the nearest date and so if I enter 30/01 this will return 20.

Is there a formula which I can use which will pick the oldest nearest price?

Thanks

[José Augusto]

Try this approach

Please Login or Register  to view this content.

where B3:B19 are items, C2:I2 are dates and C3:I19 are prices.

See the file

[mdma]

Thank you - the process is exactly what I am after. The data source of the prices, may be tabular / record rather than matrix.

I've tried adjusting as per the attached - but i get an error..

[José Augusto]

Try this if you have a version office 2010 and early

Please Login or Register  to view this content.

or this array formula for prior versions of Office (CTRL+SHIFT+ENTER instead enter to confirm the formula)

Please Login or Register  to view this content.

See the file

[mdma]

Thank you so much for your help

With some changes to the source data etc, I have now achieved what I was after. I have combined item number with another dimension, and concatenated this in the array formula - example below. (I've used ranges in the final version for performance improvement)

Thank you once again.

Please Login or Register  to view this content.