+ Reply to Thread
Results 1 to 11 of 11

How2 find number in range and return that number or if doesn't exist then the next highest

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    How2 find number in range and return that number or if doesn't exist then the next highest

    Having some trouble developing a small part of a large formula.

    I want to see if a figure in A1 exists in range B1:B10

    If it does exist i want to return that number

    If it doesn't exist then i want to return the next highest number...

    Struggling.

    p.s. i can't use an array forumla

    Any help would be appreciated!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Quote Originally Posted by Crawfinator1 View Post

    If it does exist i want to return that number

    If it doesn't exist then i want to return the next highest number...
    What if there isn't a next highest number?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-18-2013
    Location
    Tasmania, Australia
    MS-Off Ver
    Excel 2007
    Posts
    95

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Just error

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Try this...

    =INDEX(B1:B10,MATCH(TRUE,INDEX(B1:B10>=A1,0),0))

    I'm assuming the numbers in column B are sorted in ascending order.
    Last edited by Tony Valko; 06-05-2014 at 10:27 PM.

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    If the numbers in column B are not sorted you can use this:

    =IF(A1>MAX(B1:B10),#N/A,MIN(INDEX(((B1:B10>=A1)*B1:B10)+((B1:B10<A1)*1E+100),0)))

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Another......

    An Array Formula which requires confirmation with Ctrl+Shift+Enter.

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Quote Originally Posted by sktneer View Post

    An Array Formula...
    That doesn't need to be array entered.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Quote Originally Posted by Tony Valko View Post
    That doesn't need to be array entered.
    It's not true. The formula needs to be entered as an array formula to return a correct value otherwise return #N/A. The (B1:B10>=A1)*B1:B10 inside the Index requires the formula to be entered as an array. The formula you suggested in post#4, no doubt, is a regular formula. If I only use B1:B10>=A1 and don't multiply it with B1:B10 (which is unnecessary), the formula becomes regular as your formula in post#4.

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Quote Originally Posted by sktneer View Post
    It's not true. The formula needs to be entered as an array formula to return a correct value otherwise return #N/A. The (B1:B10>=A1)*B1:B10 inside the Index requires the formula to be entered as an array. The formula you suggested in post#4, no doubt, is a regular formula. If I only use B1:B10>=A1 and don't multiply it with B1:B10 (which is unnecessary), the formula becomes regular as your formula in post#4.
    Yes, but that's only because you had the syntax incorrect! Should be:

    =INDEX(B1:B10,MATCH(TRUE,INDEX((B1:B10>=A1)*B1:B10>0,0),0))

    and then it really doesn't need array-entry!

    But then you didn't need the INDEX in there at all if you were array-entering it!?

    =INDEX(B1:B10,MATCH(TRUE,((B1:B10>=A1)*B1:B10)>0,0))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    The OP said this:

    Quote Originally Posted by Crawfinator1 View Post

    p.s. i can't use an array forumla
    Otherwise, I would have already suggested some type of array formula.

  11. #11
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Also, according to their profile they're using Excel 2007 so AGGREGATE is not available.

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How2 find number in range and return that number or if doesn't exist then the next hig

    Quote Originally Posted by XOR LX View Post
    Yes, but that's only because you had the syntax incorrect! Should be:

    =INDEX(B1:B10,MATCH(TRUE,INDEX((B1:B10>=A1)*B1:B10>0,0),0))

    and then it really doesn't need array-entry!

    But then you didn't need the INDEX in there at all if you were array-entering it!?

    =INDEX(B1:B10,MATCH(TRUE,((B1:B10>=A1)*B1:B10)>0,0))

    Regards
    Perfect explanation indeed. It really helped. Thanks....

+ 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] How do I count number of values in row2 that doesn't exist in row1?
    By viking2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-27-2013, 04:11 AM
  2. Replies: 1
    Last Post: 06-10-2013, 07:08 PM
  3. [SOLVED] Formula to select the highest number, and the lowest and find out the range
    By Nero_slk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-14-2013, 07:56 AM
  4. Find highest number of sales in a date range and show seller and sale number
    By audiofreak in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2012, 03:34 AM
  5. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 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