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).
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).
Item | Date | Item | Date to Compare | Desired 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 |