Excel 2007: How to lookup a value, find latest date & return corresponding value

Hi,

I have attached a document with two sheets to help explain what I'm looking to do.

In sheet 1, I have a formula that use to work for me, however now i seem to get the error mentioned below. The formula is in Column P - named "Contracted PRICE"

"Excel ran out of resources while attempting to calculate one or more formulas. As a result, the formulas cannot be evaluated"

What i need is a working formula to look up the "Item Code" in Sheet 1
Reference the table in sheet 2 (Contract Prices) and matching the code find the latest date in Column G and return the price for that date that is in Column Y and if the item code is not found in Contract Prices Sheet then to return the Sell Price (in Sheet 1 - Column M)

eg: Lookup code 0182252, find that it appears in Contract Prices on 30/04/2012 but it also comes up on 04/03/2014 and as this is is the latest date return the value of $2.32 (Column Y) (which is the price for that date).

Hope I have explained myself well enough for some help.


This is the forumla i am current using, which works, however my page doesn't update or the formulas do not refesh as i get "Excel ran out of resources while attempting to calculate one or more formulas. As a result, the formulas cannot be evaluated" error.

=IF(MAX(('CONTRACT PRICES'!$A$8:$A$1048576=$B8)*('CONTRACT PRICES'!$G$8:$G$1048576=MAX(('CONTRACT PRICES'!$A$8:$A$1048576=$B8)*('CONTRACT PRICES'!$G$8:$G$1048576)))*'CONTRACT PRICES'!$Y$8:$Y$1048576),MAX(('CONTRACT PRICES'!$A$8:$A$1048576=$B8)*('CONTRACT PRICES'!$G$8:$G$1048576=MAX(('CONTRACT PRICES'!$A$8:$A$1048576=$B8)*('CONTRACT PRICES'!$G$8:$G$1048576)))*'CONTRACT PRICES'!$Y$8:$Y$1048576),$M8)

Thank you in advance
Rique