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 ...
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") )
=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.
OliverScheurich, 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"...