+ Reply to Thread
Results 1 to 12 of 12

How do you exclude some results on INDEX-MATCH?

  1. 12-18-2019, 04:55 AM #1
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    How do you exclude some results on INDEX-MATCH?

    Hello


    WhatsApp Image 2019-12-18 at 10.08.06.jpeg



    I'm trying to do an INDEX-MATCH to lookup A, and return B.
    I get "All Investments" as the results, I would like to see "10% Discount"
    Is it possible to manipulate the formula with some combination of IF, INDEX-MATCH, NOT (or anything else) to make excel ignore "All investments"
    I cannot change/remove the lines in the data, so that's not an option.

    Thanks a lot!!
    BM7
    Attached Files Attached Files
    Last edited by beerman7; 12-18-2019 at 05:34 AM. Reason: attaching file
    Reply With QuoteRegister To Reply
  2. 12-18-2019, 04:59 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: How do you exclude some results on INDEX-MATCH?

    Please see the yellow banner at the top of the page - it tells you how to attach your workbook.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 12-18-2019, 05:11 AM #3
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    Re: How do you exclude some results on INDEX-MATCH?

    Hi Samba,
    Thanks, I attached an image !
    BM7
    Reply With QuoteRegister To Reply
  4. 12-18-2019, 05:13 AM #4
    AliGW
    AliGW is online now
    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,905

    Re: How do you exclude some results on INDEX-MATCH?

    An image is not editable. Please attach a sample workbook, as requested.
    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
  5. 12-18-2019, 05:33 AM #5
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    Re: How do you exclude some results on INDEX-MATCH?

    Thanks! Attached a sample file
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 12-18-2019, 05:36 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: How do you exclude some results on INDEX-MATCH?

    where is your expected result?
    Reply With QuoteRegister To Reply
  7. 12-18-2019, 05:41 AM #7
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    Re: How do you exclude some results on INDEX-MATCH?

    I mentioned in the post that I want "10% Discount" to be the result
    Reply With QuoteRegister To Reply
  8. 12-18-2019, 05:57 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: How do you exclude some results on INDEX-MATCH?

    G4=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$4:C$14)/((B$4:B$14=F$4)*(C$4:C$14<>"All Investments")),ROWS(G$4:G4))),"")
    Try this, copy and paste towards down
    Reply With QuoteRegister To Reply
  9. 12-18-2019, 07:12 AM #9
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    Re: How do you exclude some results on INDEX-MATCH?

    Thanks Samba!
    It worked for the sample but not when I tried to adapt it to my Spreadsheet.
    I don't fully understand the logic of the formula..
    Does it make a difference if I use table references(Table1[A] instead of C$4:C$14 for example?
    Reply With QuoteRegister To Reply
  10. 12-18-2019, 07:02 PM #10
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: How do you exclude some results on INDEX-MATCH?

    Between the layout and the instructions I am a little confused. Please try this in F5 and fill across and see if this gives you the idea. Check the attached. Excel will adjust for your regional settings. This formula as posted probably won't.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    B
    C
    D
    E
    F
    G
    H
    3
    A
    B
    C
    4
    Option 1
    All Investments
    4.45
    All Investments
    10% Discount
    5
    Option 2
    All Investments
    4.36
    34.98
    4.50
    In F5: =SUMIF($C$4:$C$14,G$4,$D$4:$D$14)
    6
    Option 1
    10% Discount
    4.50
    7
    Option 2
    All Investments
    4.36
    8
    Option 2
    All Investments
    4.36
    9
    Option 2
    12% Discount
    4.36
    10
    Option 2
    All Investments
    4.36
    11
    Option 3
    All Investments
    4.36
    12
    Option 3
    11% Discount
    4.36
    13
    Option 3
    All Investments
    4.36
    14
    Option 2
    All Investments
    4.36
    Attached Files Attached Files
    Dave
    Reply With QuoteRegister To Reply
  11. 12-19-2019, 09:52 AM #11
    beerman7
    beerman7 is offline
    Registered User
    Join Date
    12-18-2019
    Location
    Paris
    MS-Off Ver
    365
    Posts
    16

    Re: How do you exclude some results on INDEX-MATCH?

    Hi Flame,
    Yeah i'm having a bit of trouble explaining what I need exactly.
    Each Option has multiple matches (All Investments, x% Discount)
    Since Excel reads from top-down, it returns the first match in the list, but I want it to skip "All Investments" and return the x% discount (unless the only option is All investments)
    Hope it's more clear now

    Merci
    Reply With QuoteRegister To Reply
  12. 12-19-2019, 11:56 AM #12
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: How do you exclude some results on INDEX-MATCH?

    The IFs family of functions accepts wildcards so I would suggest this.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.



    B
    C
    D
    E
    F
    G
    H
    3
    A
    B
    C
    4
    Option 1
    All Investments
    4.45
    All Investments
    % Discount
    5
    Option 2
    All Investments
    4.36
    34.98
    13.23
    In F5: =SUMIF($C$4:$C$14,"*"&G$4&"*",$D$4:$D$14)
    6
    Option 1
    10% Discount
    4.50
    7
    Option 2
    All Investments
    4.36
    8
    Option 2
    All Investments
    4.36
    9
    Option 2
    12% Discount
    4.36
    10
    Option 2
    All Investments
    4.36
    11
    Option 3
    All Investments
    4.36
    12
    Option 3
    11% Discount
    4.36
    13
    Option 3
    All Investments
    4.36
    14
    Option 2
    All Investments
    4.36
    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 giving correct results, wrong results & #N/A results...sometimes
    By mrteater in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-27-2019, 10:41 AM
  2. Using Index Match to consolidate a list and exclude an item
    By StuartAllenNZ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2018, 11:31 PM
  3. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  4. [SOLVED] Exclude Zero's from Min Function nested inside an index+Match... all within CELL funtion
    By clemsoncooz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-05-2014, 05:16 PM
  5. I want to exclude blank cell in index match formula with multiple criteria
    By NOOR8225 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-16-2013, 06:51 AM
  6. Index/match or Vlookup to exclude data
    By MZing81 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-21-2012, 03:32 PM
  7. Get value by multiple criteria (INDEX, MATCH, MAX), but exclude some results
    By viktoristus in forum Excel General
    Replies: 0
    Last Post: 03-02-2012, 11:16 AM

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