 SOLVED

New Contributor

Holiday list - Index with dynamic row

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? 5 Replies

Re: Holiday list - Index with dynamic row

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

Re: Holiday list - Index with dynamic row

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

best response confirmed by MrBond (New Contributor)
Solution

Re: Holiday list - Index with dynamic row

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

Re: Holiday list - Index with dynamic row

Thank you very much!

Re: Holiday list - Index with dynamic row

@MrBond , you are welcome