# INDEX MATCH function gives different results based on cell location

1. ## INDEX MATCH function gives different results based on cell location

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

2. ## Re: INDEX MATCH function gives different results based on cell location

Hi & welcome to the board.
=IFERROR(INDEX(B4:B9,AGGREGATE(15,6,(ROW(B4:B9)-ROW(B4)+1)/(C4:C9<=C11)/(D4:D9>=C11),1)),"")

3. ## Re: INDEX MATCH function gives different results based on cell location

Hi,

I've not identified why there is an anomaly with the existing formula. It's rather strange and there is a lot of redundancy. A C11 date >= dates in column C iwill always produce the same series of True/False values for C<- column D. i.e. any C11 date is always > column C and less than column D

The MATCH function specifiying two instances of Row(B4:B9) is always goiing to produce the same 4,5,4,6,7,8,9 array giving a 1,2,3,4,5,6 array

A better N8 formula is

Formula:
`Please Login or Register  to view this content.`

4. ## Re: INDEX MATCH function gives different results based on cell location

If the date is not in one of the holiday periods, the sumproduct will return 0 so that it's trying to return the whole of B4:B9, so without dynamic arrays it will use implicit intersection so N8 returns Fall, & N4 would return Christmas.
With the formula in C13 the implicit intersect does not work & so returns #Value

5. ## Re: INDEX MATCH function gives different results based on cell location

Thanka for the replies. The Aggregate suggestion did the trick. It also broke my brain trying to understand the explanation.

Much appreciated.

6. ## Re: INDEX MATCH function gives different results based on cell location

Glad to help & thanks for the feedback.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1