Feb 11 2020 10:21 AM - edited Feb 11 2020 10:34 AM
Hi!
I have a list with "from" and "until" dates (column A and B). In the column C, I have the type.
Next to that list I have a date list with dates from the 1st to the last day in the current month. Now, I want the type from the related row in that list. How can I receive the row in the function "INDEX" as a dynamic value?
Feb 11 2020 11:56 AM
Related row will be ROW()-ROW($A$1). But I'm not sure what you'd like to calculate, perhaps
=IF( (D$1>=$A3)*(D$1<=$B3),"U","")
Feb 11 2020 12:05 PM - edited Feb 11 2020 12:06 PM
I'd like to get the type. So, if there is for example the value "G" in C3, there must be a "G" in the cells F2 and G2.
=INDEX($A$2:$C$3;IF( (D$1>=$A3)*(D$1<=$B3);"U";"");3)
returns the error "#VALUE".
Feb 11 2020 01:00 PM
SolutionIf like this
that could be
=IF(SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)),
INDEX($C$2:$C$4,
SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)*(ROW($A$2:$A$4)-ROW($A$1)))
),
"")
Feb 11 2020 01:00 PM
SolutionIf like this
that could be
=IF(SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)),
INDEX($C$2:$C$4,
SUMPRODUCT((D$1>=$A$2:$A$4)*(D$1<=$B$2:$B$4)*(ROW($A$2:$A$4)-ROW($A$1)))
),
"")