1

I have a table from which I need to pull the next largest and/or smallest value, relative to a value in a separate table. However, I also need to allow the first table to be sortable by any criteria, without affecting the accuracy of the lookup.

I've searched and read up on various methods of using VLOOKUP, LOOKUP, HLOOKUP, and MATCH for finding next-largest/next-smallest values, but everything I find seems to require that the source column be sorted ascending/descending depending on whether you want the next-smallest/largest value. This breaks functionality when I want to be able to have both available at once, or I want to be able to sort the table without breaking the formula.

Is there a way to do a sort-insensitive search for next-largest/next-smallest values in Excel?

Preferred solutions would use native Excel functions exclusively, since I'm not currently very familiar with VBScript, and installing third-party tools is not an option at this time. Solutions must also be compatible with Excel 2010 and 2013.

  • 1
    MIN(A1..A1000) and MAX(B2..B2000) are valid functions – cybernard Jul 20 '14 at 17:26
  • You may not be familiar with VBScript, but some of us (including me) have the ability and could write a script for you. Interested? – cybernard Jul 20 '14 at 18:15
  • @cybernard If that's really the only or best way to do it, I'm interested. If there's a relatively simple way to do it with native Excel functions though, I'd prefer that. Third-party tools are still flat out of bounds. – Iszi Jul 20 '14 at 18:26
  • FYI: I know your stuck with excel but in a perfect world you would use a database. Writing a query to the data you want out is the best answer. For example: select * from table1,table2 where table1.column1>table2.column2; – cybernard Jul 20 '14 at 18:51
  • 1
    This only works well if the date and time are present. Use a blank column and copy and paste this all the way down. =IF(B3>NOW(),B3,"") I will call it column T. Then in "next up" field enter. **=MIN(T1:T1000) – cybernard Jul 20 '14 at 23:32
1

Proposed result

enter image description here

Using IF and SMALL in ARRAY formula

If the result as shown on picture is what you are looking for, the formula for finding next bigger looks like this:
=SMALL(IF(Relative[Value]>[@Value];Relative[Value];99999999999);1)

Explanation

  • Relative[Value]>[@Value] returns an array of TRUE or FALSE
  • IF(Relative[Value]>[@Value];Relative[Value];99999999999) then returns the values that are bigger from the relative table, and for those that are not, it returns some out of range huge value. Pick one that will never naturaly occur in your data. Alternatively, you can use 0 value for the FALSES, or you can work with error values.
  • Then we just use the SMALL function with argument k=1 to find first Smallest from the Bigger values.
  • It is an array formula, so enter the formula with CTRL+SHIFT+ENTER.

Links:

| improve this answer | |
0

If your numbers are unique, this will work:

Next smallest:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)+1)

Next largest:

=SMALL(YourRange,RANK.EQ(YourValue,YourRange,1)-1)

If they're not, then you can do some more complex manipulations using array formulas or helper columns. You'll also need to decide how you want to handle duplicate numbers (return the same or different value), for which you might switch to using LARGE and changing the sort order of RANK.

Still, this should give you a starting point.

| improve this answer | |
  • Can you describe the syntax some more? I'm having a hard time understanding it, based on the information in Excel's help file for SMALL. – Iszi Jul 20 '14 at 22:51
  • After some searching, it seems the RANK.EQ component is part of Data Analysis Expressions in PowerPivot - a feature not available in vanilla Excel 2010 (and with several additional prerequisites), and only available to certain editions of 2013. This won't work for me. – Iszi Jul 20 '14 at 23:00
  • 1
    Can you provide a sample of a few cells in the unsorted table and then a little bit of the 2nd table that is trying for a relative value? – bvaughn Oct 12 '16 at 21:21
0

Use a blank helper (evil) column and copy and paste this all the way down. =IF(B3>NOW(),B3,"") I will call it column T. Then in "next up" field enter. **=MIN(T1:T1000)

As a function something like this:

    Function Soonest(scolumn As String) As Date

'
'
Dim a, b
Dim test(20000) As Date
Dim Min As Date

b = 0

   For a = 1 To 20000
    If (IsEmpty(Range(scolumn & a))) Then
     GoTo SkipMe
    End If

     If (Range(scolumn & a).Value - Now() > 0) Then
     b = b + 1
     test(b) = Range(scolumn & a).Value
     End If
SkipMe:
  Next a

  If b = 0 Then
   Min = "None"
  GoTo NoneFound
  End If
  Min = test(1)
  For c = 1 To b
   If test(c) < Min Then Min = test(c)
  Next c
NoneFound:

Soonest = Min
End Function
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.