英文:
Find closest date of matching date an array in google sheets
问题
let(Σ,如果无(filter(B:B,A:A=C2,B:B<=C3,B:B>C3-20)),
xlookup(C3,Σ,Σ,,-1))
英文:
I'm looking to find the closest matching earlier date for a given name that also restricts values and adapts the following formula which works on an individual line level to work if it was just pasted in E2.
let(Σ,ifna(filter(B:B,A:A=C2,B:B<=C3,B:B>C3-20)),
xlookup(C3,Σ,Σ,,-1))
In the table below the values in D:E are used to look up and find corresponding values in A:B returning the nearest date that is not less than 20 days earlierin F:F. How can this be reproduced by simply placing one formula (array, map etc) in E2?
| name | earlier date | name | later date | output | formula text | |
|---|---|---|---|---|---|---|
| john | 3/25/2023 | john | 3/25/2023 | 3/25/2023 | =let(Σ,ifna(filter(B:B,A:A=D3,B:B<=E3,B:B>E3-20)),xlookup(E3,Σ,Σ,,-1)) | |
| salley | 3/26/2023 | salley | 3/26/2023 | 3/26/2023 | =let(Σ,ifna(filter(B:B,A:A=D4,B:B<=E4,B:B>E4-20)),xlookup(E4,Σ,Σ,,-1)) | |
| john | 3/29/2023 | john | 3/27/2023 | 3/25/2023 | =let(Σ,ifna(filter(B:B,A:A=D5,B:B<=E5,B:B>E5-20)),xlookup(E5,Σ,Σ,,-1)) | |
| salley | 3/30/2023 | salley | 3/28/2023 | 3/26/2023 | =let(Σ,ifna(filter(B:B,A:A=D6,B:B<=E6,B:B>E6-20)),xlookup(E6,Σ,Σ,,-1)) | |
| john | 3/31/2023 | john | 3/29/2023 | 3/29/2023 | =let(Σ,ifna(filter(B:B,A:A=D7,B:B<=E7,B:B>E7-20)),xlookup(E7,Σ,Σ,,-1)) |
答案1
得分: 1
=map(D2:D,E2:E,lambda(d,e,if(d="",,let(Σ,ifna(filter(B:B,A:A=d,B:B<=e,B:B>e-20)),xlookup(e,Σ,Σ,,-1)))))
英文:
You may try:
=map(D2:D,E2:E,lambda(d,e,if(d="",,let(Σ,ifna(filter(B:B,A:A=d,B:B<=e,B:B>e-20)),xlookup(e,Σ,Σ,,-1)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论