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.

onlyor 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:26bestanswer. For example: select * from table1,table2 where table1.column1>table2.column2; – cybernard Jul 20 '14 at 18:51=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