MATCH or VLOOKUP based on most recent date

jack109

New Member
Joined
May 10, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
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.


TIPSTERS.jpg


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
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,944
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 10, 2020
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Cheers looks like it worked, actually corrected the mistakes I made manually lol.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,717
Messages
5,597,729
Members
414,169
Latest member
Preston_Cleric

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top