+ Reply to Thread
Results 1 to 6 of 6

INDEX MATCH function gives different results based on cell location

  1. #1
    Registered User
    Join Date
    08-13-2016
    Location
    NL
    MS-Off Ver
    2013
    Posts
    4

    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
    Attached Files Attached Files
    Last edited by tisko; 02-10-2021 at 11:45 AM. Reason: Solved

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,585

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

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

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,638

    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: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,585

    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. #5
    Registered User
    Join Date
    08-13-2016
    Location
    NL
    MS-Off Ver
    2013
    Posts
    4

    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. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    10,585

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

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VBA Formula unexpected behaviour
    By chungiemo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2020, 03:35 AM
  2. [SOLVED] Index Match across two sheets giving unexpected results
    By Sprong in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-30-2019, 04:02 PM
  3. [SOLVED] Removing Exact Text String from giving Excel Cells - Unexpected Behaviour
    By ghoneim in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-05-2013, 08:42 AM
  4. [SOLVED] MATCH function - unexpected #NA result
    By broncotundra in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2013, 08:52 PM
  5. [SOLVED] Using Index Match or Vlookup giving unexpected results on imported data
    By jacob@thepenpoint in forum Excel General
    Replies: 2
    Last Post: 07-03-2012, 05:49 PM
  6. [SOLVED] Unexpected (?) behaviour of OFFSET() in array formulas
    By vezerid in forum Excel General
    Replies: 11
    Last Post: 12-13-2005, 04:30 PM
  7. [SOLVED] Uh-oh, unexpected behaviour. Commandbar shows up in all workbooks and doesn't close down.
    By StargateFanFromWork in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-09-2005, 12:15 PM

Bookmarks

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