+ Reply to Thread
Results 1 to 7 of 7

Index match function with multiple arrays

  1. #1
    Registered User
    Join Date
    07-25-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    63

    Index match function with multiple arrays

    Hi,
    I am using the following function, to search/match the set of arrays (each letter is a named range that has already been set). My
    Please Login or Register  to view this content.
    in the space marked ******** I have tried to add the following code (to limit the second array to only the first column) (I know I could just create another set of named ranges but I was looking for a shortcut )
    Please Login or Register  to view this content.

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2010.. mostly
    Posts
    14,342

    Re: Index match function with multiple arrays

    i think we need to see a sample sheet.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution is also shown (mock up the results manually).

    3. Make sure that all confidential information is removed first!!

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    07-25-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    63

    Re: Index match function with multiple arrays

    Thanks for your reply, I have generated a test spreadsheet
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,343

    Re: Index match function with multiple arrays

    I am not sure I understand what you are expecting in the arrays. The formula in the upload suggests using a seldom used 4th argument in INDEX called Area_num.

    Try array entering this formula in D4.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    With "one" in D3 it returns {1;2;3;4;5} and with "two" it returns {6;7;8;9;10}

    Is this what you are expecting?
    Dave

  5. #5
    Registered User
    Join Date
    07-25-2017
    Location
    England
    MS-Off Ver
    2010
    Posts
    63

    Re: Index match function with multiple arrays

    Hi,
    With "one", I was expecting it to return "1", i.e. will search both column k and o for a match and then return the value in the cell in the column parallel to it

  6. #6
    Forum Expert
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,343

    Re: Index match function with multiple arrays

    Try array entering this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    XP - 2007 & Win10 - 2013
    Posts
    6,343

    Re: Index match function with multiple arrays

    Another array formula ... shorter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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