How do I find the next smallest/largest value without sorting?

Question

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.

Proposed result

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:

• SMALL function
• ExcelJet nth smallest value with criteria
• MS Guidelines and examples of array formulas
• ExcelIsFun Youtube series: Ctrl + Shift + Enter: Excel Array Formulas

Comments

[cybernard]

MIN(A1..A1000) and MAX(B2..B2000) are valid functions

[cybernard]

You may not be familiar with VBScript, but some of us (including me) have the ability and could write a script for you. Interested?

[Iszi]

@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.

[cybernard]

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;

[Iszi]

@cybernard I can't count how many times I've been told "Excel is not a database" around here. Unfortunately, it is indeed what I'm stuck with - not only by limitations of my own knowledge, but also the same limitations existing in the knowledge of the peers who need to be able to work with the data similarly. Plus, getting funding to support a proper database, and build an application on top of it, isn't easy.

[cybernard]

In the field to find the next smallest/largest value are there exact duplicate values? FYI: Using for example, MS Access, you can hide the inner working behind a graphical user interface and then only a few people need to know the inner workings.

[cybernard]

In EXCEL try filters. Highlight the column with the field names and click filter. Now each column should have a drop down you can filter with. A custom auto filter will allow you to do ranges. This isn't 100% automated but it seem simplier than 100% manual.

[Iszi]

@cybernard Thanks for the info. The ranged being searched will have duplicates, but this is inconsequential to my purposes for now. Right now, I'm trying to search a non-ordered list (or, at least, one which may end up ordered by something other than time) of past and upcoming events and return the nearest past and future times in that list relative to the present.

[Iszi]

@cybernard Example: A bunch of things are supposed to happen exactly an hour from now, and are listed on one sheet along with a whole bunch of other events and times which might not necessarily be in a given order. My other sheet has a field for the "next time something's going to happen" - it doesn't really care what's specifically going to happen, or how many things are going to happen at the same time. It just wants to know when the next time is (in this case, exactly an hour from now) that something, anything, is going to happen.

[cybernard]

"next time something is going to happen is" =MIN(B1:B43) Will give you the lowest number from a list of date/time field. The only issue is if the list contains dates/times from the past. Obviously change B1:B43 to match your spreadsheet.

[Iszi]

@cybernard The list will contain past events. So, wouldn't that instead return "the first time something happened"? This is why I'm looking for something that will do next highest/lowest matching.

[cybernard]

Are the date and time stored in the same column or as 2 different columns? Can you give me the exact format/example of 1 time field? Is it like 7/12/2014 05:00PM?

[cybernard]

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)

[Iszi]

@cybernard Ah, helper columns. Not ideal, but it works.

[Iszi]

FTR: The dates & times are indeed in the same cell, stored in Excel's date-time format. So 7/12/2014 17:00 is effectively 41832.70833.

[cybernard]

I know it is not ideal, but we are forced to operate using the wrong tools. FTR SQL example: select * from table where table1.date>now order by table1.date limit 1

[Iszi]

@cybernard Want a good laugh? This is to support a spreadsheet that's manually populated based on data from several instances of another application that are using separate Access databases. If it was using a SQL database, and all instances used the same database, I could query the whole thing at once - while the application instances were still live - and be done. So, I'm using the wrong tool to support another wrong tool which is itself using the wrong tool.

[cybernard]

You have my sympathy. SQL queries can be done in MS Access, in design/sql view, I have done many that way. Now the big question are Access databases on the network where you can access them? I could instruct you on using the ODBC connector to access both of them on your PC, but that is crazy talk. I know, sorry.

[Iszi]

@cybernard I could get to them over the network, but they're locked - can't even read them - when the application is running. And there are over a dozen. Making all of them talk to one SQL database is in the near-future to-do list.

[Iszi]

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]

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.

[bvaughn]

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?