+ Reply to Thread
Results 1 to 11 of 11

INDEX / MATCH with multiple row results

  1. 03-21-2023, 07:02 PM #1
    MJHEED
    MJHEED is offline
    Registered User
    Join Date
    03-21-2023
    Location
    Ohio, USA
    MS-Off Ver
    2019
    Posts
    7

    INDEX / MATCH with multiple row results

    Hello all. I hope one of you guru's can lend a hand. I've been trying for 2 days to make this work.

    I've attached a sample excel sheet "IndexMatch-multi-row".
    Inside there are rows of people. Each person has an "AET" assigned in row 2. There is also a list of "allowed servers" in each person's row.

    I'd like to create a formula that when I enter a server number, such as 37, it will find all people with a 37 in their row, and display their "AET".
    I'm trying to create a list of "AET" values that match the "allowed servers". I've given a manually entered example.
    In my example, I entered "37" in A33 to specify "which server #".
    The correct formula would search all rows for a "37" and begin listing their "AET" values as such:
    FIFTEEN
    SIXTEEN
    SEVENTEEN
    EIGHTEEN

    I've tried several variations of INDEX / MATCH, but I can only get the first answer.

    Any help would be greatly appreciated.
    Thank you.

    MJ
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-21-2023, 07:16 PM #2
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: INDEX / MATCH with multiple row results

    try Power Query
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 03-21-2023, 07:48 PM #3
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,498

    Re: INDEX / MATCH with multiple row results

    Cell A26 formula , Drag down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 03-21-2023, 08:00 PM #4
    MJHEED
    MJHEED is offline
    Registered User
    Join Date
    03-21-2023
    Location
    Ohio, USA
    MS-Off Ver
    2019
    Posts
    7

    Re: INDEX / MATCH with multiple row results

    Sandy666, I'm confused.... why does your sheet have no formulas, and wants to access external data sources?
    Attached Images Attached Images
    Last edited by MJHEED; 03-21-2023 at 08:05 PM.
    Reply With QuoteRegister To Reply
  5. 03-21-2023, 08:04 PM #5
    MJHEED
    MJHEED is offline
    Registered User
    Join Date
    03-21-2023
    Location
    Ohio, USA
    MS-Off Ver
    2019
    Posts
    7

    Re: INDEX / MATCH with multiple row results

    wk9128, I think you nailed it. I'm going to test this on my full data set tomorrow. THANK YOU!!!
    Reply With QuoteRegister To Reply
  6. 03-21-2023, 08:08 PM #6
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,498

    Re: INDEX / MATCH with multiple row results

    @MJHEED You're Welcome. Glad to help . Thank You for the feedback.


    If you finally get a solution please mark your thread as SOLVED:
    - Click Thread Tools above your first post,you will see the word PREFIX on the upper left, press the button to select [SOLVED] select "Mark your thread as Solved".
    - you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help
    was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  7. 03-21-2023, 08:23 PM #7
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: INDEX / MATCH with multiple row results

    Quote Originally Posted by MJHEED View Post
    Sandy666, I'm confused.... why does your sheet have no formulas, and wants to access external data sources?
    no formula because this is a Power Query

    don't worry about the warning, just accept it

    source is yours so trust your own source
    Last edited by sandy666; 03-21-2023 at 08:25 PM.
    Reply With QuoteRegister To Reply
  8. 03-22-2023, 07:52 AM #8
    MJHEED
    MJHEED is offline
    Registered User
    Join Date
    03-21-2023
    Location
    Ohio, USA
    MS-Off Ver
    2019
    Posts
    7

    Re: INDEX / MATCH with multiple row results

    Quote Originally Posted by wk9128 View Post
    Cell A26 formula , Drag down

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    wk, I applied the formula to my workbook. The form with this formula is on another tab, so I modified it to point to the page with the data "People" and the range of cells for my data. The matching server number is on the same page with the formula in cell C3:

    =IFERROR(INDEX(People!B:B,AGGREGATE(15,6,ROW(People!$D$2:$AJ$50)/(People!$D$2:$AJ$50=$C$3),ROW(1:1))),"")

    After applying CSE to make it array, I drag down to copy the formula to lower cells, noting that ROW(1:1) is the only thing changing.
    But, for some reason it is not working. All results are blank. No errors.
    It appears to be an issue with the server number it is comparing... in your formula that was =$A$33 ... in mine I changed it to =$C$3. If I remove the cell reference and just enter the server number into the formula =37 it works perfectly.
    I even tried replacing the cell reference $C$3 with the formula that is in C3 "=VLOOKUP($B$3,serversdata,6,FALSE)", but that did not help either. It seems to have issues going between pages/tabs?

    Thanks again for the help!
    MJ
    Last edited by MJHEED; 03-22-2023 at 08:37 AM.
    Reply With QuoteRegister To Reply
  9. 03-26-2023, 10:53 PM #9
    JeteMc
    JeteMc is offline
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,681

    Re: INDEX / MATCH with multiple row results

    We might be better able to help if you could show us a sample workbook that produces the behavior mentioned in post #8.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  10. 03-29-2023, 02:48 PM #10
    MJHEED
    MJHEED is offline
    Registered User
    Join Date
    03-21-2023
    Location
    Ohio, USA
    MS-Off Ver
    2019
    Posts
    7

    Re: INDEX / MATCH with multiple row results

    Thank you JeteMc, and wk9128. It was indeed the cell reference. I bypassed that and it's working great now!
    Last edited by MJHEED; 03-29-2023 at 02:52 PM.
    Reply With QuoteRegister To Reply
  11. 03-31-2023, 08:27 PM #11
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,498

    Re: INDEX / MATCH with multiple row results

    @MJHEED You're Welcome. Glad to help . Thank You for the feedback and rep.
    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)

Similar Threads

  1. [SOLVED] INDEX-MATCH with multiple results stops returning valid results
    By keshavtale in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-01-2022, 12:24 PM
  2. Need Help with Index, Match, Match with multiple results and copying data x times
    By jrboyd in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-27-2019, 06:57 AM
  3. Index match multiple results
    By luke_reed in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2017, 08:06 AM
  4. Index Match with multiple results
    By pitterpatton in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-10-2014, 10:41 PM
  5. Index/Match with multiple results
    By kwadjo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-26-2013, 04:05 PM
  6. Index/Match with multiple results
    By amcghee1 in forum Excel General
    Replies: 4
    Last Post: 10-18-2012, 12:15 PM
  7. Need help with Index + Match Multiple Results
    By xenohadden in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2012, 05:00 PM

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