VLOOKUP based on date in specific timeframe

Hello everyone,

I have a list of prices on purchase orders that needs to be compared with reference prices from another huge list (over 100.000 lines).

The list with the reference prices can contain two or sometimes more lines for the same material, having different validity periods (e.g. price valid from 01.10.2011 till 30.09.2012 and another line saying from 01.10.2012 till 30.09.2013, etc).

The VLOOKUP needs to compare the prices from both lists but should always only compare with the relevant price (i.e. the one which was valid at time of delivery). Therefore the first list also has a column with the delivery date.

How can I ensure that the VLOOKUP always only returns the value from the valid line?
In my experience VLOOKUP always returns the first match it finds in a list which in my scenario is not necessarily the case.
My approach so far looks like this but I am afraid that the last VLOOKUP always returns me the first match it finds:

Please Login or Register  to view this content.

To make this even more complex, there can be cases where one validity period lies within another, eg.: material X has a reference price valid from 01.01.2010 - 31.12.9999 and another reference price valid from 01.10.2012 - 30.09.2013. It is always the "inner" price that is relevant, so if this material was delivered on 02.10.2012 the second price was valid, whereas if it is going to be delivered on 01.10.2013, the first price is valid. But both cases would be true for my IF function above. Is there a way for Excel to make that distinction?

Thank you very much for help!

Originally Posted by Pepe Le Mokko

Please post a sample sheet.
In any case the syntax you are using for VLOOKUP is incorrect

Hi Pepe,

attached, please find an example sheet.

The main purpose is to compare column "J" on the "comparison" sheet with column "G" on the "reference_prices" sheet. But, as explained above, there can be prices with different periods on the reference sheet. In column "V" on the comparison sheet I tried to build this condition into the VLOOKUP but so far it doesnt work. As an example, see line 2 on comparison sheet, which shows OK but there are two entries on the reference sheet and only one of them was relevant at time of delivery.

I'm interested in the solution too. Please someone help 4134x4nd3r !