Feb 17 2022 11:39 AM
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
Feb 17 2022 12:02 PM
=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.
Feb 17 2022 01:20 PM
Feb 17 2022 01:44 PM
=IF(NOT(ISNA(INDEX(T_Vacc[Name],MATCH(1,(D3=T_Vacc[Name])*(E3<=T_Vacc[vaccination date])*(F3>=T_Vacc[vaccination date]),0)))),INDEX(T_Vacc[vaccination date],MATCH(1,(D3=T_Vacc[Name])*(E3<=T_Vacc[vaccination date])*(F3>=T_Vacc[vaccination date]),0)),"No")
Do you want to return the date between the hospitalization dates?
This formula returns the day of vaccination between the from and to date of hospitalization in my sheet. If there isn't a within date the formula returns No instead of a between date.
Feb 18 2022 01:37 PM
SolutionAs 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")
)
Feb 18 2022 01:57 PM
=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.
Feb 19 2022 06:21 AM
Feb 19 2022 06:23 AM
Feb 18 2022 01:37 PM
SolutionAs 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")
)