+ Reply to Thread
Results 1 to 12 of 12

INDEX MATCH -> multiple results from 2 columns

  1. #1
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    INDEX MATCH -> multiple results from 2 columns

    Hello,

    I'm trying to get 2 multiple results from 2 different columns under 1 column and in order.

    Column E = Matching Apple in column A -> returning C value
    {=INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($A$1:$A$6,$E$1,0)),MATCH(ROW($A$1:$A$6),ROW($A$1:$A$6)),""),ROWS($A$1:A1)))}

    Column F = Matching Apple in column B -> returning negative C value
    {=-INDEX($C$1:$C$6,SMALL(IF(ISNUMBER(MATCH($B$1:$B$6,$F$1,0)),MATCH(ROW($B$1:$B$6),ROW($B$1:$B$6)),""),ROWS($B$1:B1)))}

    Column G = THE GOAL to get results like this

    Cross-posted at https://www.mrexcel.com/board/thread...lumns.1146688/ --6SJ
    Last edited by paupaj; 09-30-2020 at 06:21 AM.

  2. #2
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,314

    Re: INDEX MATCH -> multiple results from 2 columns

    Hi @paupaj

    I suppose you don't want that, but it produces the results you say you want (array formula).
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    Re: INDEX MATCH -> multiple results from 2 columns

    Thanks, Josť Augusto,

    But yeah, I want the results exaclty like in G column...

  4. #4
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    Re: INDEX MATCH -> multiple results from 2 columns

    ideas? anyone?

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,278

    Re: INDEX MATCH -> multiple results from 2 columns

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  6. #6
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    Re: INDEX MATCH -> multiple results from 2 columns

    Sorry about that, I wasn't aware.

    I get the following message though:
    "You are not allowed to post any kinds of links, images or videos until you post a few times."

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    16,436

    Re: INDEX MATCH -> multiple results from 2 columns

    I have edited your post to add the link since you are not yet eligible. We prohibit links by new users because mostly it's spam.

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,278

    Re: INDEX MATCH -> multiple results from 2 columns

    Sorry about that, I wasn't aware.
    Then you seem to have forgotten to read the forum rules?
    Perhaps refresh your memory before going ny further, it will make life easier for you and for your fellow members
    Links can always be added as text stripped of the http part
    Last edited by Pepe Le Mokko; 09-25-2020 at 10:39 AM.

  9. #9
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    Re: INDEX MATCH -> multiple results from 2 columns

    I have modified my sheet to illustrate the issue better.

    I am trying to match D1 value from both A & B columns and get back the result from C column. Values matching B column have to be opposite.
    I want to skip values that are not found in both A & B columns and return values only when there is a match.

    D2 formula: {=(A2:A11=$D$1)*C2:C11-(B2:B11=$D$1)*C2:C11}

    Column E = THE GOAL to get results like this.

    Attachment 697042
    Last edited by paupaj; 09-26-2020 at 07:53 AM.

  10. #10
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    10,695

    Re: INDEX MATCH -> multiple results from 2 columns

    Selecting Attachment 697042 in post #9 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator"
    Please utilize the instructions in the banner at the top of the page to upload an .xlsx file.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  11. #11
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    6,477

    Re: INDEX MATCH -> multiple results from 2 columns

    Pls see attachment:
    Rebuilt with non array formula:

    E2:
    =IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($A$1:$A$6)/($A$1:$A$6=E$1),ROW(1:1))),"")

    F2:
    =IFERROR(INDEX(-$C$1:$C$6,AGGREGATE(15,6,ROW($A$1:$A$6)/($B$1:$B$6=F$1),ROW(1:1))),"")

    Then G2, array formula:
    =IFERROR(INDEX(IF($A$1:$A$6=G$1,$C$1:$C$6,IF($B$1:$B$6=G$1,-$C$1:$C$6,"")),AGGREGATE(15,6,ROW($A$1:$A$6)/(($A$1:$A$6=G$1)+($B$1:$B$6=G$1)),ROW(1:1))),"")

    Ctrl-shift-enter
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-15-2020
    Location
    Vilnius, Lithuania
    MS-Off Ver
    MS Office 2010
    Posts
    15

    Re: INDEX MATCH -> multiple results from 2 columns

    @bebo021999
    Finally - the results without any additional/side calculations.
    Thank you so much Sir!

+ 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. 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
  2. Index match multiple results
    By luke_reed in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2017, 08:06 AM
  3. Multiple results Index Match
    By vortexx in forum Excel General
    Replies: 4
    Last Post: 05-09-2016, 03:51 AM
  4. [SOLVED] Sum of multiple index/match results
    By kawaik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-25-2015, 10:03 PM
  5. Index Match with multiple results
    By pitterpatton in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-10-2014, 10:41 PM
  6. Index/Match with multiple results
    By amcghee1 in forum Excel General
    Replies: 4
    Last Post: 10-18-2012, 12:15 PM
  7. Index and Match and multiple results
    By mike2bf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-06-2008, 04:10 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