Closest Date While Matching Value

WinterTT

New Member
Joined
Nov 8, 2012
Messages
20
Hello,
I've been trying to come up with a single formula that will look at an item and its date to compare against a different similar list. The goal is to pull the closest date for that particular item. I see that getting the closest date isn't all too hard {=MAX(IF(A2:A5<=C2,A2:A5))} but how would I go about making this formula only apply to items in the desired list (Apples will only look at Apple's dates) while not modifying the data itself or using VBA (which would be way easier for me).

ItemDate ItemDate to CompareDesired result (Closest Date)
Apple
1/1/2020​
Apple
1/10/2020​
1/1/2020​
Apple
2/15/2020​
Apple
2/1/2020​
2/15/2020​
Pear
4/15/2020​
Pear
4/15/2020​
4/15/2020​
Orange
2/1/2020​
Orange
2/2/2020​
2/2/2020​
Grapes
3/15/2020​
Grapes
3/15/2020​
3/15/2020​
Pear
4/14/2020​
Pear
4/1/2020​
4/14/2020​
Orange
2/1/2020​
Orange
3/1/2020​
2/4/2020​
Orange
2/2/2020​
Orange
3/2/2020​
2/4/2020​
Orange
2/3/2020​
Orange
3/3/2020​
2/4/2020​
Orange
2/4/2020​
JackFruit
3/4/2020​
N/A
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I can't tell from your example what data is where!

Maybe this will help, A is the 1st Item Column

=MAX(IF(($A$2:$A$11=D2)*($B$2:$B$11<=E2),$B$2:$B$11))
 
Upvote 0

Forum statistics

Threads
1,214,984
Messages
6,122,601
Members
449,089
Latest member
Motoracer88

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
Back
Top