Return latest value (by date) within a date range

Hi,

See attached.

In column P, I want to return the latest $/L rate from column I, for a specific property, based on dates (in column D) before the last day of a specified month (in column L).

I'm not sure which type of formula to use but in excel terms:

1. Match M1 to Array G3:G100 (Property match)
2. Return latest (by date) rate from I3:I100 ($/L range array) if D3:D100 (Date range array) <= EOMONTH(L1,0) (month)

So for example: Cell P1 should equal $1.1733 (that is Property = Pegunny, latest date in January = 19/01/2018)


Please let me know if i need to clarify anything, cheers

Hi Ben

Can you advise what (c1:c15) relates to in the first formula?

Also how do you apply the dynamic range formula so that it has a criteria of property and < specified date?

Thanks for your help Silva