+ Reply to Thread
Results 1 to 9 of 9

values returned by index function without duplicates?

  1. #1
    Registered User
    Join Date
    02-14-2020
    Location
    Pune
    MS-Off Ver
    1
    Posts
    4

    values returned by index function without duplicates?

    I need to extract values in sorted order from column E falling in year 2019 (column D) and return and display appropriate match from Column C.

    Formula entered in cell G3 is “={IFERROR(INDEX($C$3:$C$14, MATCH(LARGE(IF($G$2=$D$3:$D$14, $E$3:$E$14, ""), ROW(A1)), IF($G$2=$D$3:$D$14, $E$3:$E$14, ""),0)),"")}”

    Output is as desired but includes duplicate entries as well. How do I display distinct values only dynamically? I need to accept duplicates coming from column E but while displaying the month in column G, I need to show only distinct months.
    Attached Images Attached Images
    Last edited by Pepe Le Mokko; 02-14-2020 at 06:37 AM. Reason: Removed part of title. We know you are here for help

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: values returned by index function without duplicates?

    Administrative note
    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile does not indicate your version.
    Thanks

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: values returned by index function without duplicates?

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  4. #4
    Registered User
    Join Date
    12-26-2019
    Location
    seoul, south korea
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: values returned by index function without duplicates?

    Press Ctrl, Shift, Enter key

    g2
    =IFERROR(INDEX($C$3:$C$13,MATCH(MAX(IF(($D$3:$D$13=G$2)*(COUNTIF(G$2:G2,$C$3:$C$13)=0),$E$3:$E$13)),IF(($D$3:$D$13=G$2)*(COUNTIF(G$2:G2,$C$3:$C$13)=0),$E$3:$E$13),0)),"")

  5. #5
    Registered User
    Join Date
    02-14-2020
    Location
    Pune
    MS-Off Ver
    1
    Posts
    4

    Re: values returned by index function without duplicates?

    Perfect. It worked. Thank you OnRainbow!!

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: values returned by index function without duplicates?

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, 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.

  7. #7
    Registered User
    Join Date
    02-14-2020
    Location
    Pune
    MS-Off Ver
    1
    Posts
    4

    Re: values returned by index function without duplicates?

    At the outset, i thought the output is as it should be. However, on closer looks, it seems it is taking first max value and ignoring the other values.

    For instance, It displayed Jan19 first before Apr19.
    The corresponding Novel value for Jan19 is 4000 & 500 whereas for Apr19 it is 2323 & 3100.

    How about having it sort based on cumulative values provided in Novel column? So, output would be Oct19, Dec19, Apr19, Jan19 and Mar19.

  8. #8
    Registered User
    Join Date
    12-26-2019
    Location
    seoul, south korea
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: values returned by index function without duplicates?

    Press Ctrl, Shift, Enter key

    =IFERROR(INDEX($C$3:$C$13,MATCH(MAX(IF(($D$3:$D$13=G$2)*(COUNTIF(G$2:G2,$C$3:$C$13)=0),SUMIF($C$3:$C$13,$C$3:$C$13,$E$3:$E$13))),IF(($D$3:$D$13=G$2)*(COUNTIF(G$2:G2,$C$3:$C$13)=0),SUMIF($C$3:$C$13,$C$3:$C$13,$E$3:$E$13)),0)),"")

  9. #9
    Registered User
    Join Date
    02-14-2020
    Location
    Pune
    MS-Off Ver
    1
    Posts
    4

    Re: values returned by index function without duplicates?

    Thank you onrainbow. This helps!

+ 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. Replies: 1
    Last Post: 12-11-2019, 01:42 AM
  2. Multiple Values Returned in INDEX
    By tgill91 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-26-2019, 02:04 PM
  3. [SOLVED] Values not returned by Index, Match when using values from Data Validation
    By Avinashch in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-10-2015, 10:34 AM
  4. Index Match returning values when none should be returned
    By Khaos1208 in forum Excel General
    Replies: 6
    Last Post: 04-27-2014, 04:48 PM
  5. Need VBA code to search and display multiple returned values
    By shawnduthie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-15-2013, 05:16 AM
  6. [SOLVED] Index function with hlookup to display values located on another page
    By PhoenixFaery in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-18-2013, 09:10 PM
  7. Average All Values Returned by Index?
    By dracogram in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-23-2010, 02:39 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1