I’m looking to find the closest matching earlier date for a given name and to be able to restrict the number of days.
For example, say john’s value 3/30/23, and I want to find any occurence within 20 days of 3/20/23 but not later than that date, then the formula will try to find a match of name & date in A:A, B:B and return a date within a specified time period.
In the table below I will provide a name (C2) date (C3), and I might also give a limit of days (C4).
| Name | Date | Value |
|---|---|---|
| john | 3/27/2023 | john |
| salley | 3/28/2023 | 3/30/2023 |
| john | 3/29/2023 | 20 |
| salley | 3/30/2023 | formula |
| john | 3/31/2023 |
The formula should return 3/29/2023 in C5.
My formula doesnt seem to be working and instead is returning the first match.
ArrayFormula(INDEX(B1:B, MATCH(MIN(IF((A1:A=C1)*(B1:B<=C2)*(C2-B1:B<=20), C2-B1:B, 999999)), 0)))
>Solution :
You may try:
=let(Σ,ifna(filter(B:B,A:A=C2,B:B<=C3,B:B>C3-20)),
xlookup(C3,Σ,Σ,,-1))
