英文:
Find closest date of matching value earlier than a given date in google sheets
问题
ArrayFormula(INDEX(B1:B, MATCH(MIN(IF((A1:A=C1)(B1:B<=C2)(C2-B1:B<=20), C2-B1:B, 999999)), 0))) 的目的是找到给定名称和时间范围内最接近的较早日期。在你的示例中，对于名称“john”和日期“3/30/23”，它会查找在20天范围内的匹配项，但不晚于该日期。它会搜索A列中的名称和B列中的日期，然后返回在指定时间段内的日期。
在你的示例中，公式应该返回3/29/2023到C5。
英文:
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)))
答案1
得分: 1
=let(Σ,如果无法找到(filter(B:B,A:A=C2,B:B<=C3,B:B>C3-20)),
xlookup(C3,Σ,Σ,,-1))
英文:
You may try:
=let(Σ,ifna(filter(B:B,A:A=C2,B:B<=C3,B:B>C3-20)),
xlookup(C3,Σ,Σ,,-1))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论