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.