+ Reply to Thread
Results 1 to 4 of 4

Arrary functions and Index(Match)) replicating return.

  1. #1
    Registered User
    Join Date
    08-31-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    2

    Arrary functions and Index(Match)) replicating return.

    I have set up the following function:

    {=INDEX(K514:K522, MATCH(TRUE, K514:K522="",0)-2)}

    This is the range K514:K522 for clarity.

    Salaries and benefits
    Subcontractors
    Utilities & Property Tax
    Repair and maintenance
    Bank charges and interest
    (Blank)
    (Blank)
    (Blank)



    This looks through the array K514:K522 for the first empty cell then returns the item 2 rows before it. I have created an array of 8 of this formula moving down, so the -2 returns repair and maintenance, the -3 returns utilities and property tax, etc. Where I run into an issue is with -5, which returns Salaries and Benefits as intended but -6 also returns Salaries and Benefits. -7 returns a #VALUE error. I am unsure of what is causing this replication.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Arrary functions and Index(Match)) replicating return.

    Hi mwensel. Welcome to the forum.

    INDEX returns whole columns / rows when 0 is the argument. You saw Salaries and Benefits because with 0 as the row argument it was the first in a column array. If you click in the formula bar and hit the F9 function key you will see

    {"Salaries and benefits";"Subcontractors";"Utilities & Property Tax";"Repair and maintenance";"Bank charges and interest";0;0;0}

    or

    {"Salaries and benefits";"Subcontractors";"Utilities & Property Tax";"Repair and maintenance";"Bank charges and interest";"";"";""}

    -7 is of course results in -1.

    Did this help?
    Dave

  3. #3
    Registered User
    Join Date
    08-31-2017
    Location
    Vancouver, Canada
    MS-Off Ver
    2013
    Posts
    2

    Re: Arrary functions and Index(Match)) replicating return.

    Thanks Dave, that helps. I can work with this now.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Arrary functions and Index(Match)) replicating return.

    Good deal. Thanks for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. [SOLVED] Replicating a 2 variable index and match
    By HarryGreenwood in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-06-2017, 01:42 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. Replies: 5
    Last Post: 10-16-2016, 02:33 AM
  4. Replies: 14
    Last Post: 04-09-2015, 12:43 PM
  5. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  6. Replies: 3
    Last Post: 05-08-2013, 02:10 PM
  7. Replies: 6
    Last Post: 07-10-2010, 05:16 AM

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