+ Reply to Thread
Results 1 to 12 of 12

Formula to index values based on criteria - in numerical order

  1. #1
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Formula to index values based on criteria - in numerical order

    Hi,

    Got another tough one here.. My apologies for the complex questions (at least for me it is)... As usual, permission to change title if you find a better fit for the problem.

    I am in need of an index/match formula that returns a list of numbers from a data set that are GREATER THAN or LESS THAN a value, where the returned list is in ASCENDING ORDER, and is only of UNIQUE values of the data set. My biggest excel weakness is INDEX/MATCH formulas with criteria.

    Please see attached spreadsheet for a sample of what I am trying to achieve. Let me know if any questions arise.

    EDIT:
    The solution can be multiple formulas in different columns to manipulate the data. As long as the solution is the same, I don't care what the method is. Also, if possible, please return #NA for any FALSE conditions.


    Thank you very much!
    Attached Files Attached Files
    Last edited by ironfelix717; 11-22-2017 at 12:20 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Formula to index values based on criteria - in numerical order

    Please clarify what the output should look like by adding the required result. I'm assuming it should show the following

    I11: 1.39
    I23: 1.39
    I32: 1.33
    I36: 1.34
    I37: 1.34

    J13: 1.56
    J15: 1.67
    J18: 1.55
    J19: 1.78
    J20: 1.56
    J24: 1.57
    J31: 1.55
    J33: 1.56
    J34: 1.56
    J38: 1.61
    J39: 1.56
    J40: 1.61
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Based on your list:

    I11: 1.39
    I23: 1.39
    I32: 1.33
    I36: 1.34
    I37: 1.34

    J13: 1.56
    J15: 1.67
    J18: 1.55
    J19: 1.78
    J20: 1.56
    J24: 1.57
    J31: 1.55
    J33: 1.56
    J34: 1.56
    J38: 1.61
    J39: 1.56
    J40: 1.61

    IF Criteria is: Lower limit = 1.4, Upper Limit = 1.6

    Than result should be:
    1.33
    1.34
    1.39
    NA#
    ......


    Another column for upper limit, result should be:

    1.61
    1.67
    N/A#...
    ...
    ...


    Thank you very much

  4. #4
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Note; Values for data set in your list should all be in same column.

  5. #5
    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
    79,375

    Re: Formula to index values based on criteria - in numerical order

    There are two columns in your sample data ... Please clarify what you want. We expect you to show the expected outcome in your sample file, so if two columns is NOT what you want, then show us what you DO want instead, please.
    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.

  6. #6
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Hi,

    See attached please.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Quote Originally Posted by AliGW View Post
    There are two columns in your sample data ...
    The RESULTS are to be stored in two columns. The raw data is one column, which if you notice above, Richard uses both I and J columns for his raw data and is the reason I made that vague comment.

    Thanks

  8. #8
    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
    79,375

    Re: Formula to index values based on criteria - in numerical order

    Richard was querying you results columns I and J, asking if his interpretation of your requirements was correct. Your raw data is in column C. I think you have probably misunderstood what Richard was saying.

  9. #9
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Hi,

    Understood. In that case, the results are incorrect. My latest file (above) includes manually calculated results for him to reference.


    Thank you

  10. #10
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Formula to index values based on criteria - in numerical order

    Try in K9:

    =IFERROR(SMALL(IF(($C$3:$C$34<>"")*($C$3:$C$34<$K$4)*(COUNTIF($K$8:K8,$C$3:$C$34)=0),$C$3:$C$34),1),"")

    in L9:

    =IFERROR(SMALL(IF(($C$3:$C$34<>"")*($C$3:$C$34>$K$5)*(COUNTIF($L$8:L8,$C$3:$C$34)=0),$C$3:$C$34),1),"")

    Array formula, enter with Ctrl+Shift+Enter.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Formula to index values based on criteria - in numerical order

    Another way.

    Array entered in I9.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Array entered in J9.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  12. #12
    Forum Contributor
    Join Date
    08-22-2017
    Location
    USA
    MS-Off Ver
    2016 WINDOWS and MAC
    Posts
    294

    Re: Formula to index values based on criteria - in numerical order

    Phuocam,

    Works beautifully! Thank you very much. Thanks to everyone else who helped out.

+ 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. [SOLVED] Numerical Incident Order based on Department Name
    By Anarchus in forum Excel General
    Replies: 7
    Last Post: 01-24-2017, 12:11 AM
  2. [SOLVED] Help with an Index Match formula so that it return values in order (dates)
    By michelle 1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-23-2016, 11:40 AM
  3. [SOLVED] Sort Numerical-With-Dash Values In Order
    By swordswinger710 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-22-2015, 11:54 AM
  4. [SOLVED] INDEX Formula - Want to list names in Numerical Order
    By mlbdc2012 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-01-2015, 12:06 PM
  5. [SOLVED] Lookup and display values in order based on different date criteria.
    By sandman85 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-13-2014, 02:57 AM
  6. Need help with Data Validation Formula for a set of numerical and text-based values
    By bozhidar.ivanov in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 04-30-2014, 10:32 AM
  7. Macro to rearrange cells based on numerical order
    By Mbutler132000 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2010, 11:02 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1