+ Reply to Thread
Results 1 to 10 of 10

Index and Match Function across multiple array

  1. 08-10-2012, 11:22 AM #1
    Ray Park
    Ray Park is offline
    Registered User
    Join Date
    08-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Index and Match Function across multiple array

    Hey everyone

    I'm experiencing a little difficulty in trying to apply the match function to multiple arrays. My goal here is basically to use it across multiple data sets (matrices)

    This first formula works perfectly but only applies to one data set

    =INDEX(A1:Z13,MATCH(T18,A1:A13,0),MATCH(T19,A2:Z2,0))

    I try to apply this to two and I get a #value! error in

    =INDEX(A1:AW13,MATCH(Y18,A1:A13&AB1:AB9,0),MATCH(Y19,A2:AW2,0))

    I've tried multiple formulas and cant seem to get it to work. Any help will be appreciated. Thanks!

    Ray
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-10-2012, 11:43 AM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP 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
    29,464

    Re: Index and Match Function across multiple array

    Hi,

    Although it's not explicitly mentioned, when the help says it looks in a contiguous array, my understanding is that a contiguous array means a single row or column of cells. Try using two Index statements concatenated, with the second match element in each wrapped in an IFERROR() statement. to trap a not found item.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 08-10-2012, 11:51 AM #3
    Ray Park
    Ray Park is offline
    Registered User
    Join Date
    08-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Index and Match Function across multiple array

    Hello

    I'm a bit confused as to how the IFERROR() formula would be able to correct my formula. If the formula is limited to only a singular array, is there any other way I can accomplish searching in multiple arrays? Thank you.
    Reply With QuoteRegister To Reply
  4. 08-10-2012, 12:33 PM #4
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP 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
    29,464

    Re: Index and Match Function across multiple array

    Hi,

    What I'm suggesting is use
    =IF(ISERROR(INDEX(A1:AW13,MATCH(Y18,A1:A13,0),MATCH(Y19,A2:AW2,0))),"",INDEX(A1:AW13,MATCH(Y18,A1:A13,0),MATCH(Y19,A2:AW2,0)))&IF(ISERROR(INDEX(A1:AW13,MATCH(Y18,AB1:AB9,0),MATCH(Y19,A2:AW2,0))),"",INDEX(A1:AW13,MATCH(Y18,AB1:AB9,0),MATCH(Y19,A2:AW2,0)))

    If you had Excel 2007 of course that could be much simplified.
    Reply With QuoteRegister To Reply
  5. 08-10-2012, 01:37 PM #5
    Ray Park
    Ray Park is offline
    Registered User
    Join Date
    08-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Index and Match Function across multiple array

    I do use excel 07. I cant seem to follow how that formula was able to work however in using the ""

    How would I simply this? Thank you so much for the help. I have been spending the last week trying to make it work.
    Reply With QuoteRegister To Reply
  6. 08-10-2012, 02:57 PM #6
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP 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
    29,464

    Re: Index and Match Function across multiple array

    Hi,

    If you are therefore using Excel 2007 it would be a good idea to update your profile


    =IFERROR(INDEX(A1:AW13,MATCH(Y18,A1:A13&AB1:AB9,0),MATCH(Y19,A2:AW2,0)),"")&IFERROR(INDEX(A1:AW13,MATCH(Y18,A1:A13&AB1:AB9,0),MATCH(Y19,A2:AW2,0)),"")
    Reply With QuoteRegister To Reply
  7. 08-10-2012, 03:06 PM #7
    Ray Park
    Ray Park is offline
    Registered User
    Join Date
    08-03-2012
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Index and Match Function across multiple array

    There is no result when I try that formula. Can I continue the formula if I have more matrices?
    Reply With QuoteRegister To Reply
  8. 08-10-2012, 07:07 PM #8
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP 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
    29,464

    Re: Index and Match Function across multiple array

    Quote Originally Posted by Ray Park View Post
    There is no result when I try that formula. Can I continue the formula if I have more matrices?
    Hi,

    Yes, you can just concatenate other arrays as I've done with your original two.
    However you don't say whether you expect a result in this first case. The implication is that you do. In which case try each of the individual INDEX functions on their own and work out why one is not giving you a result before you extend the formula further.
    Reply With QuoteRegister To Reply
  9. 09-04-2023, 03:24 AM #9
    Midoya
    Midoya is offline
    Registered User
    Join Date
    02-19-2013
    Location
    UAE
    MS-Off Ver
    Excel 2019
    Posts
    44

    Re: Index and Match Function across multiple array

    Hello!

    I have an issue with my Index Match formula, I want to develop an automatic weekly plan to be generated from the main sheet " Study Plan" where I've listed all lessons against all subjects and made dates vertically. the weekly Plan tab where I want to generate the data from "Study Plan" Tab.230904 Study Plan - Grade 3.xlsx
    Reply With QuoteRegister To Reply
  10. 09-04-2023, 03:28 AM #10
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Index and Match Function across multiple array

    Administrative Note:

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1