Lookup() Sheet2.C:C where Date is less than cell in Sheet1

[cchan]

Greetings. Any logic/formula suggestions appreciated… I have a CALC file with Sheet1 and Sheet2. Sheet2 contains 3 columns, “bill”, date (DD/MM/YYYY), amount. Sheet1 contains B1 = Manually typed date in DD/MM/YYYY format. Columns A2:A & B2:B contain each budget Bill name and I want column B to lookup() Sheet2.A:A and match Sheet1.$A:A (bill name), however, not include records in Sheet2 that have dates greater than the date typed into Sheet1.B1. EXAMPLE.ods - Google Drive ← example sheet. I have it semi working with SUMIFS, however, obviously i dont want to tally ALL matches, i just want it to return the last record found that matches. Similarly I am having SOME luck with lookup() but can not find a working solution to make it test the date.

First time posting … hope google drive link to example file is okay. Any help appreciated.

[JohnSUN]

Welcome!

MAXIFS() will help you find the desired date by two conditions

=MAXIFS($Sheet2.B:B;$Sheet2.B:B;"<"&($B$1+1);$Sheet2.A:A;$A2)

Now you need to decide what value you want to receive if there is more than one value in your list for this date and for this resource (after all, this can happen, people can be wrong, right?)
You may want the maximum, minimum, average, or total value (Or display a warning about incorrect data?).

=MAXIFS(    $Sheet2.C:C;$Sheet2.B:B;MAXIFS($Sheet2.B:B;$Sheet2.B:B;"<"&($B$1+1);$Sheet2.A:A;$A2);$Sheet2.A:A;A2)
=MINIFS(    $Sheet2.C:C;$Sheet2.B:B;MAXIFS($Sheet2.B:B;$Sheet2.B:B;"<"&($B$1+1);$Sheet2.A:A;$A2);$Sheet2.A:A;A2)
=AVERAGEIFS($Sheet2.C:C;$Sheet2.B:B;MAXIFS($Sheet2.B:B;$Sheet2.B:B;"<"&($B$1+1);$Sheet2.A:A;$A2);$Sheet2.A:A;A2)
=SUMIFS(    $Sheet2.C:C;$Sheet2.B:B;MAXIFS($Sheet2.B:B;$Sheet2.B:B;"<"&($B$1+1);$Sheet2.A:A;$A2);$Sheet2.A:A;A2)

If there is only one value for the found date and the specified category, all these formulas will return the same result

[cchan]

Champion! Thank you.