Hi,
I'm running into some behaviour of an INDEX function that I'm not able to explain.
I found a formula on internet that allows me to check if a date-value in a certain cell falls within the date range of a school holiday. If so, it should display the name of the holiday, if not it should display nothing.
The problem is that it apparently matters which row I run the INDEX-formula in. If the formula is ran in the same row as the INDEX-array, and the input date is in none of the date ranges, it doesnt return the NO VALUE error. Instead, it returns the value in the array that is located in the same row. This even transfers over sheets.
I'm stumped as to how the same formula can produce different results based on its location in the document.
The Formula:
=INDEX(B4:B9,SUMPRODUCT((C11>=C4:C9)*(C11<=D4:D9)*MATCH(ROW(B4:B9),ROW(B4:B9))))
Where B4:B9 is the array with holiday names
Where C11 is de input value
Where C4:C9 is the array with starting dates
Where D4:D9 is the array with end dates
I've added a file as attachement.
Can anyone help me out?
Best regards,
Thijs
Bookmarks