MATCH or VLOOKUP based on most recent date

[jack109]

Hi

Is there a way that a formula can look up a range of dates and then use vlookup perhaps to pull data from a certain column based on the row that has the most recent date.

What I want to do is for excel to calculate what £ per unit I should be using from the data in columns A - C. I would like column L to use a formula to determine this. So using the date is column J, I want the formula to lookup the closest date in column A, and return the value in column C, that also matches the Tipster in in column B using column K.

Hopefully I've explained this clearly


Thanks

 

[jasonb75]

Try this in L2 and fill down

=MAXIFS(C:C,B:B,K2,A:A,MAXIFS(A:A,B:B,K2,A:A,"<="&J2))

Might be possible with LOOKUP and FILTER but I don't have 365 to try those.

 

[jack109]

Cheers looks like it worked, actually corrected the mistakes I made manually lol.