SOLVED

Contributor

# 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

7 Replies

# Re: Lookup date from one table within date range in another table

``=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.

# Re: Lookup date from one table within date range in another table

Hello
Thank You very much.
And if I want to return the passed vaccination date instead of Yes/No?
Zdenek

# Re: Lookup date from one table within date range in another table

``=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.

best response confirmed by Zdenek_Moravec (Contributor)
Solution

# Re: Lookup date from one table within date range in another table

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")
)``````

# Re: Lookup date from one table within date range in another table

``=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.

# Re: Lookup date from one table within date range in another table

@Quadruple_Pawn, thank You very much for a quick response. This works perfect! I have been using Excel for 25 years, but still don't know, from which end to start to build a megaformula by solving tasks like this ... then I see Your response and I say "exactly this way"...

# Re: Lookup date from one table within date range in another table

@ Sergei Baklan, thank You for a modern solution. This is the way, how to learn new functions ...