Search for a particular date in sheet 1 with FROM and TO dates in 2 columns and then paste data from a column in same row in sheet 1 into sheet 2

Question

I am trying to get this formula to work but can't here is link:

https://docs.google.com/spreadsheets/d/1PmUqg-G-TEZhTFj_viqy3XY0KSiJJiVVGDcN8VV57uI/edit?usp=sharing

Array Formula to Lookup Date Between Two Dates:
Master Formula:

=ArrayFormula(IF(LEN(F2:F),(VLOOKUP(F2:F,{row(indirect("A"&A2):indirect("A"&B5)),transpose(split(join("",(rept(C2:C5&"|",(B2:B5-A2:A5)+1))),"|"))},2,FALSE)),))

Make a copy of your spreadsheet without personal data and update your question, to understand it is better that we can look at the case visually! — Brondby IF
I have added the link and put a description of what I am trying to do in cells E2 to E5 on "Calendar 21" sheet — harry
I have added comments on sheet called JDT Test to show how suggested solution is not looking between the start and and End dates and returning text "FREE" when the selected date in Column F does not appear in the dates in columns A & B ie date 30/1/2021 should return the text "FREE" as the date 30/1/2021 is not within any of the date rages in Columns A & B — harry

player0 player0 · Accepted Answer · 2020-03-11T01:21:21

1

votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(F2:F, {A2:A, C2:C}, 2, 1)))

Search for a particular date in sheet 1 with FROM and TO dates in 2 columns and then paste data from a column in same row in sheet 1 into sheet 2

1 Answers