Find the Closest Date to Today

[Nanogirl21]

How can I search similar to vlookup to return the matching date that is nearest to today? The search term will have multiple matches, but I only want to return the nearest date. A future or current date is always preferred, but if not available the nearest past date is acceptable.

Example:

Apple	1/5/2022
Grape	7/7/2022
Apple	4/2/2022
Pear	5/2/2022
Apple	1/11/2022
Apple	5/2/5000

Search: Apple
Today Date: 2/8/2022
Expected Result: 4/2/2022

Thank you.

 

[BSALV]

i use european dateformat dd/mm/yy, so the formula is okay for both dateformats, but the result here are okay in my eyes.
Use that formula with all mm/dd/yy-dates and the result 'll also be okay.

Map1
	A	B	C	D	E	F
1	Apple	1/05/2022		5/02/22	nearest in future and past
2	Grape	7/07/2022		1/05/22	nearest in future
3	Apple	4/02/2022
4	Pear	5/02/2022
5	Apple	1/11/2022
6	Apple	5/02/5000
Blad1

Cell Formulas
Range	Formula
D1	D1	=INDEX(B1:B6,MATCH(MIN(ABS(B1:B6-TODAY())),ABS(B1:B6-TODAY()),0))
D2	D2	=INDEX(B1:B6,MATCH(MIN(IF(B1:B6>=TODAY(),B1:B6-TODAY(),"")),IF(B1:B6>=TODAY(),B1:B6-TODAY(),""),0))

 

[Nanogirl21]

=INDEX(B1:B6,MATCH(MIN(IF(B1:B6>=TODAY(),B1:B6-TODAY(),"")),IF(B1:B6>=TODAY(),B1:B6-TODAY(),""),0))

Thank you. How do I identify which keyword the formula is looking for? I am looking to only find the date that matches the word Apple, not the entire range.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Apple	05/01/2022		Apple	02/04/2022
3	Grape	07/07/2022		Pear	01/01/2022
4	Apple	02/04/2022
5	Pear	02/05/2021
6	Apple	11/01/2022
7	Apple	02/05/5000
8	Pear	01/01/2022
9
Primary

Cell Formulas
Range	Formula
E2:E3	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),XLOOKUP(TODAY(),f,f,XLOOKUP(TODAY(),f,f,,-1),1))

 

[Nanogirl21]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Apple	05/01/2022		Apple	02/04/2022
3	Grape	07/07/2022		Pear	01/01/2022
4	Apple	02/04/2022
5	Pear	02/05/2021
6	Apple	11/01/2022
7	Apple	02/05/5000
8	Pear	01/01/2022
9
Primary

Cell Formulas
Range	Formula
E2:E3	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),XLOOKUP(TODAY(),f,f,XLOOKUP(TODAY(),f,f,,-1),1))

This worked. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Nanogirl21]

How about

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Apple	05/01/2022		Apple	02/04/2022
3	Grape	07/07/2022		Pear	01/01/2022
4	Apple	02/04/2022
5	Pear	02/05/2021
6	Apple	11/01/2022
7	Apple	02/05/5000
8	Pear	01/01/2022
9
Primary

Cell Formulas
Range	Formula
E2:E3	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),XLOOKUP(TODAY(),f,f,XLOOKUP(TODAY(),f,f,,-1),1))

HI, I've been playing around with your solution and am running into a problem.

Currently, the formula is matching and pulling the first date in the series that is a match. It is not matching the date that is nearest to today's date. How can I adjust the formula?

Example:
2/14/2019
4/2/2022
4/1/2026

The formula is returning 2/14/2019 as the result. If I sort the data it will then show 4/1/2026 as the result. However, the closest to today (02/15/2022) is 4/2/2022. I am expecting 4/2/2022 as the answer.

 

[Fluff]

That's the answer I get.

+Fluff 1.xlsm
	A	B	C	D	E
1
2	Apple	14/02/2019		Apple	02/04/2022
3	Grape	07/07/2022		Pear	01/01/2022
4	Apple	02/04/2022
5	Pear	02/05/2021
6	Apple	02/04/2022
7	Apple	01/04/2026
8	Pear	01/01/2022
9
Main

Cell Formulas
Range	Formula
E2:E3	E2	=LET(f,FILTER($B$2:$B$100,$A$2:$A$100=D2),XLOOKUP(TODAY(),f,f,XLOOKUP(TODAY(),f,f,,-1),1))

Check that your dates are not text