CODEWITHSUNDEEP
google-sheets
Nuri Tasdemir
Nuri Tasdemir

Reputation: 9842

lookup on date ranges in google spreadsheet

I have a google spreadsheet which looks like this. Note that KeyColumn values are from a finite pool.

StartDate      EndDate       KeyColumn
01/01/2016     05/01/2016    Key1
05/01/2016     10/01/2016    Key2
11/01/2016     20/01/2016    Key1

They are sorted. Next StartDate is greater than or equal to the current EndDate.

What I want to do is creating the following sheet with a kind of lookup. Each key column can be created manually. 

Date           Key1          Key2
01/01/2016     True          False
02/01/2016     True          False
03/01/2016     True          False
04/01/2016     True          False
05/01/2016     True          True
06/01/2016     False         True

02/01/2016 is True for Key1 because it is in the range of the first row in the first table which has the value Key1.

05/01/2016 is True for Key1 and Key2 because it is in the range of the first row and the second row.

Is this possible in google spreadsheet without using any scripts? I couldn't find a way.

Upvotes: 0

Views: 1234

Answers (2)

javis_mcqueen
javis_mcqueen

Reputation: 181

This can be accomplished with creative use of the filter and count functions:

enter image description here

Upvotes: 1

user6637260
user6637260

Reputation:

i used query function to solve that the formula

=iferror(hlookup(B$8,transpose( QUERY({arrayformula(days($A$2:$B$4,)),$C$2:$C$4},"select Col3 where Col1<="& days($A9,) &" and Col2>= " & days($A9,) ,-1)),1,False)=B$8,False)

or

=iferror(hlookup(B$8,transpose(query($A$2:$C$4,"Select C Where toDate(A) < date '" & text(A9, "yyyy-mm-dd") & "' " & " and toDate(B) > date '" & text(A9, "yyyy-mm-dd") & "' ",-1)),1,False)=B$8,False)

enter image description here

Upvotes: 0

Related Questions