Forum Discussion

Brass Contributor

Feb 17, 2022

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

Lookup date in date range.xlsx12 KB

excel

Formulas and Functions

SergeiBaklan

Feb 18, 2022

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

Lookup date in date range.xlsx16 KB

OliverScheurich

Gold Contributor

Feb 17, 2022

Zdenek_Moravec

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