Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Index matching multiple inputs and returning multiple results
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index matching multiple inputs and returning multiple results

    BLDG ROOM ZONE BLDG (input) Room (input)
    10 204 U02
    10 101 U01 Zone (Return)
    22 188 U04 (Return)
    10 213 U01
    43 110 U10


    Having little success trying to get the proper return data.

    Using the input of the building and room number, I am trying to get the corresponding zones as a return. There are sometimes multiple returns, so I also need those displayed a well.

    I have been able to get a single return using his formula -


    =INDEX(A2:C6,MATCH(E1&F1,A2:A6&B2:B6,0),3)

    But I have been unsuccessful at getting multiple returns.

    Tanks in advance or the assistance.

  2. #2
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Assuming E1 = building number and F1 = room number...

    Code:
    {=INDEX(C2:C6,MATCH(F1,IF(A2:A6=E1,B2:B6),0))}
    (also make sure you press CTRL+SHIFT+ENTER as this is an array formula)
    Last edited by svendiamond; Dec 15th, 2014 at 06:33 PM.

  3. #3
    New Member
    Join Date
    Mar 2013
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Svendiamond thanks for the formula- i've been searching high and low for an example like this. Thanks

  4. #4
    New Member
    Join Date
    Dec 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Svendiamond, I plugged it in and I get value not valid error. I went full scale though (1900 rows) so I'll scale it down and see if I can find the error.

  5. #5
    New Member
    Join Date
    Dec 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Svendiamond, I got it to work, however, I had to insert a ,3 after the 0

    {=INDEX(I6:K14,MATCH(N6,IF(I6:I14=M6,J6:J14),0),3)}

    But it doesn't solve the issue of multiple zones.

    Some buildings have multiple zones, so when I enter the building number and room number, I need a return on the zones available.
    I will be using that returned data to return VLAN numbers and IP ranges...

  6. #6
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Well... I don't know why you're INDEXing three columns when you only want what's in column K... right? I thought you just wanted to return the Zone that matches both the Building and Room?

  7. #7
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Quote Originally Posted by eaykan View Post
    Svendiamond thanks for the formula- i've been searching high and low for an example like this. Thanks
    Yeah no problem. I remember when I learned this... I just Googled it. I mean if you think about it it makes a lot of sense:

    FIND the C2:C6 that matches F1 in B2:B6 -- as long as A2:A6 equals E1

  8. #8
    New Member
    Join Date
    Dec 2014
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    Quote Originally Posted by svendiamond View Post
    Well... I don't know why you're INDEXing three columns when you only want what's in column K... right? I thought you just wanted to return the Zone that matches both the Building and Room?
    BLDG ROOM ZONE BLDG (input) Room (input)
    10 204 U02
    10 204 U01 Zone (Return)
    22 188 U04 (Return)
    10 213 U01
    43 110 U10


    Correct. i need the zone (column K), but I need all the zones that building is in. I already a formula to get a single return, which is -



    {=INDEX(I6:K14,MATCH(M6&N6,I6:I14&J6:J14,0),3)}


    Which is basically the same as what you provided, however, With your formula, I get an invalid cell reference area unless I put the ,3 to return the data in the third column.


    If you look at the example above, Building 10, RM 204 falls into two zones. U01 and U02. When I put the search criteria in, I want it to return both zones so I can later have
    it reference VLANS and IP ranges.


    It's hard to make the explanation clear in plain text...

  9. #9
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,253
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    See if this gives you what you want.
    If you set up the cell references properly you can just copy the formula in F3 down (change cell references to match your data).
    This is an array formula and must be entered with CTRL-SHIFT-ENTER.

     ABCDEFG
    1BLDGROOMZONEBLDG10Room204
    210204U02    
    310204U01 ZoneU02 
    422188U04  U01 
    510213U01    
    643110U10    

    Spreadsheet Formulas
    CellFormula
    F3{=IFERROR(INDEX($C$2:$C$6,SMALL(IF($A$2:$A$6&$B$2:$B$6=$E$1&$G$1,ROW($C$2:$C$6)-ROW($C$2)+1),ROWS($C$2:C2))),"")}
    F4{=IFERROR(INDEX($C$2:$C$6,SMALL(IF($A$2:$A$6&$B$2:$B$6=$E$1&$G$1,ROW($C$2:$C$6)-ROW($C$2)+1),ROWS($C$2:C3))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

  10. #10
    Board Regular svendiamond's Avatar
    Join Date
    Jun 2014
    Location
    USA
    Posts
    1,406
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index matching multiple inputs and returning multiple results

    ***Just saw AhoyNC's answer... it might be easier and work better for you!


    No no... I understand. I was thinking you might say that. This will be a bit more complicated.

    Name your table something. So... I6:K14 right? Name that table "TABLE" -- then we have M6 and N6 as the data you want to look up right? So M6 is the BLDG # and N6 is the ROOM # that you're going to look up.

    I don't know how your sheet looks but I started my return table in M11. M11 through M15 say "ZONES" and N11 through N15 have the following formula filled down. You can go down as far as you want, based on how many different results you think you may end up with:

    =IFERROR(INDEX(TABLE, SMALL(IF(COUNTIFS($M$6, $I$7:$I$14, $N$6, $J$7:$J$14), ROW(TABLE)-MIN(ROW(TABLE))+1), ROW(C1)), COLUMN(C1)),"")

    (array formula... press CTRL+SHIFT+ENTER)

    Notice the $I$7:$I:$11 reference... yes, ignore the headers in your table when referencing. This should work for you!

    ---------------------------------

    Check out Match two criteria and return multiple rows in excel | Get Digital Help - Microsoft Excel resource for more information on how this array formula works.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •