+ Reply to Thread
Results 1 to 8 of 8

INDEX & MATCH Function with Multiple Arrays

  1. #1
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    Lightbulb INDEX & MATCH Function with Multiple Arrays

    Hello,

    I have several data tables that I would like to reference from and return the row of values for the corresponding variable. I am able to use the Index and Match function for one array table, but I cannot figure out to input multiple arrays in the same function. My ultimate goal is to have several large data tables on one excel sheet as a form of directory. My second sheet will be used to pull certain variables from the directory and return the corresponding values. I started out using the VLOOKUP function, but read several posts explaining how MATCH and INDEX is more appropriate for this situation. I don't know if I am going about solving this issue correctly, but any help will be appreciated.

    My formula for C2 is as follows: =(INDEX(C5:C15,MATCH($B$2,$B$5:$B$15,0))). For example, inputting "Date" in cell B2 will return "7" in C2, "4" in D2, "8" in E2, and "4" in F2. I would like to be able and enter say, "Kale" from the second array table to get the correct data, but that is where I am stuck.

    This is my first time posting on this forum, so I hope my screen attachment posted correctly and is visible. If not, please let me know so I can try and display my excel sheet example.

    Thank in advance for the help.



    Screen Shot 2014-10-21 at 10.47.14 PM.png
    Last edited by btone; 10-22-2014 at 02:10 AM.

  2. #2
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: INDEX & MATCH Function with Multiple Arrays

    hi,

    Upload a sample workbook instead of image , make sure it is no having any confidential data

    Punnam

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: INDEX & MATCH Function with Multiple Arrays

    =index(h5:H15,match(c2&d2&e2&f2,c5:c15&d5:d15&e5:e15&f5:f15,0)
    this is to fine "kale" when c2=4 , d2=47 ,e2=73 , f2= 378
    Use ctrl+Shift+Enter

    Punnam

  4. #4
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    Re: INDEX & MATCH Function with Multiple Arrays

    Punnam,

    Thank you for your quick reply. I have attached the worksheet for your reference.I would like to enter any fruit from the list and return the corresponding variables. For example I would like to enter, "Durian" in cell B2 and have excel return "39" in C2, "8" in D2, "8" in E2, and "9" in F2.

    Thank you.


    Macro Trial.xlsxMacro Trial.xlsx

  5. #5
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,887

    Re: INDEX & MATCH Function with Multiple Arrays

    hi,

    your requirement is not clear ,

    Is this what you are trying to achieve

    Punnam
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    Post Re: INDEX & MATCH Function with Multiple Arrays

    Punnam,

    I apologize for the unclear goals. The formula you created ONLY works with the table on the right. I tried to enter "Apple" on your formula, but received #N/A for all 4 cells. I would like ONE formula to reference BOTH of the array tables. For example, I could have 50 array tables, but would like only ONE formula to reference all the data from all the tables.

    Please look at the attached worksheet. I have included instructions on the sheet that I believe will make more sense. Also, I have included two more tables.

    Thank you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-20-2014
    Location
    israel
    MS-Off Ver
    2007
    Posts
    21

    Re: INDEX & MATCH Function with Multiple Arrays

    Hi Btone,
    I think that's what you are looking for,
    Macro Trial solution.xlsx

    Regars,
    Eran.

  8. #8
    Registered User
    Join Date
    10-22-2014
    Location
    San Diego, CA
    MS-Off Ver
    Excel 2011
    Posts
    44

    Re: INDEX & MATCH Function with Multiple Arrays

    Eran,

    Thank you very much for inputting the function. It is exactly what I am looking for. I need to study up on the formula, because I would like to replicate it for a larger data set. Thank you again!

    Best,

    Brandon

+ 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. Problem with reference to multiple arrays in INDEX/MATCH combo
    By tnuis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-24-2013, 08:10 PM
  2. Problems with reference to multiple arrays using VLOOKUP / INDEX-MATCH
    By tnuis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2013, 07:21 AM
  3. Sum Index Match Arrays
    By CaesarBob in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-19-2012, 04:38 AM
  4. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM
  5. Replies: 7
    Last Post: 03-26-2009, 12:13 PM

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