I hope someone can solve this for me as I can’t see how to do it:
One of the columns in my accounts spreadsheet contains dates (in ascending order) and another column contains the account balances (at those dates). To produce a report automatically I want to enter a period end date in a cell, then identify the balance at the highest date that is less than or equal to that period end date.
So, in the (ascending) date range A2:A256, locate the highest date <= date in k2 and return the balance at that date in balance range G2:G256.
Hallo
if we can assume the dates are in ascending order AND unique:
=VLOOKUP(K2;$A$2:$G$256;7)
That’s brilliant, many thanks karolus. I thought it might be a problem as the dates are not unique, but I’ve tried it and it correctly selects the last duplicate date within the criterion.