+ Reply to Thread
Results 1 to 5 of 5

index return nth large value

  1. #1
    Registered User
    Join Date
    05-16-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    3

    index return nth large value

    Hello, I got a very confusing problem. Attached please find the sample sheet.

    what I want to get is the 2nd large value from column E to Y based on discount amount in column A.

    However, the first function in cell B2 works, but the second one in B4 does NOT. #NUM error. Why?

    My excel is 2010 and 2013, both did not work.

    error message.JPG

    I spent hours trying to fix but no clue totally. Appreciate anyone can help. Tkx.
    Attached Files Attached Files
    Last edited by double7671; 05-24-2019 at 09:25 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: index return nth large value

    Your formula gives the wrong answer if the values are all negative. I'm not sure why it fails in the second case, but then again I don't see why it works at all.

    This formula works for all cases. It is also an array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-16-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    3

    Re: index return nth large value

    Hey, Jeff, thank you for sharing your solution.

  4. #4
    Registered User
    Join Date
    05-16-2019
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    3

    Re: index return nth large value

    Hello, I have further question, if I want to get the valid value at most right position in the array,

    say, I want B2 returns Y2 value (98%), B4 returns W4 value (498%), how can I write the formula?

    Thanks again.

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: index return nth large value

    Responding to post #4, try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that this is not an array formula so that it may be activated by pressing the Enter key alone.
    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.

+ 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. Index / Large Help
    By wpm7113 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-02-2017, 12:46 PM
  2. [SOLVED] IF <=0 INDEX MATCH LARGE otherwise return blank
    By augr in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 11-14-2016, 05:34 PM
  3. Index with large data
    By shhj in forum Excel General
    Replies: 2
    Last Post: 04-26-2016, 08:32 AM
  4. [SOLVED] Return Top 5 Products by Branch using Index and Large
    By Hiuwah in forum Excel General
    Replies: 18
    Last Post: 04-29-2015, 10:55 AM
  5. How to: Return next value w/ same # in index match large function?
    By Yoshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2015, 02:35 AM
  6. Return Row Index and Column Index of a Cell in a Range
    By exceere in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 01-06-2015, 04:54 AM
  7. Replies: 4
    Last Post: 03-13-2013, 12:38 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