Forum Discussion
Solved
Lookup date from one table within date range in another table
Dear colleagues,
I have a table with list of names and their vaccination date. Next table contains the names and when they were hospitalized from – to.
| Vaccination table | Hospital table | |||||
| Name | vaccination date | Name | Hospitalized from | Hospitalized to | Was hospitalized in the time of vaccination? | |
| Adam | 15.02.2021 | Adam | 01.01.2021 | 14.02.2021 | No | |
| Adam | 15.03.2021 | Bart | 25.02.2021 | 28.02.2021 | Yes | |
| Adam | 15.04.2021 | Cindy | 04.12.2021 | 10.12.2021 | Yes | |
| Bart | 26.01.2021 | |||||
| Bart | 26.02.2021 | |||||
| Bart | 26.03.2021 | |||||
| Cindy | 05.10.2021 | |||||
| Cindy | 05.11.2021 | |||||
| Cindy | 05.12.2021 |
The task is to find for every name if the hospitality date from – to passes with the vaccination date.
I was thinking of filtering vaccination table by name and use this result in xlookup? Or double xlookup?
Thank You for any hint.
Zdenek Moravec
As variant
=LET( d, FILTER( T_Vacc[vaccination date], T_Vacc[Name] = [@Name] ), check, (MAX(d) >= [@[Hospitalized from]]) * (MIN(d) <= [@[Hospitalized to]]), IF(check, "Yes", "No") )
7 Replies
As variant
=LET( d, FILTER( T_Vacc[vaccination date], T_Vacc[Name] = [@Name] ), check, (MAX(d) >= [@[Hospitalized from]]) * (MIN(d) <= [@[Hospitalized to]]), IF(check, "Yes", "No") )- @ Sergei Baklan, thank You for a modern solution. This is the way, how to learn new functions ...
=IF(NOT(ISNA(INDEX(T_Vacc[Name],MATCH(1,(D3=T_Vacc[Name])*(E3<=T_Vacc[vaccination date])*(F3>=T_Vacc[vaccination date]),0)))),"Yes","No")Maybe with this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
- Hello
Thank You very much.
And if I want to return the passed vaccination date instead of Yes/No?
Zdenek=IFERROR(INDEX(T_Vacc[vaccination date],LARGE(IF((D3=T_Vacc[Name])*(E3>=T_Vacc[vaccination date]),ROW($1:$9)),1)),"No")Do you want to return the last vaccination date before the hospitalized from date? Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
