Function to search a list (unsorted) and find location of a value close to a specified var

Here is what I am trying to do.
I have a list of numerical data taken from a measurement that is not sorted and can't be sorted. Here is the reason why. The values start near 0 then increase up to a max number then decrease back down to zero and do the same thing on the negative end of things. I have to find a point on the increasing positive side (e.g. 2000) and then that same point on the decreasing positive side. The number it finds just needs to be the closest match to the number we are looking for.

The same thing needs to be done on the negative side as well.

Below is an example of two points that would be on the curve. Think of the max in the upper right where the two lines meet being ~10000 and the arrows being at a value close to the variable we are looking for (2000).

Any help would be appreciated

hysteresis.gif

Originally Posted by Brendan_Floyde

Post an example spreadsheet with de-sensitized data. Sounds like a simple lookup question but hard to tell. B is 2000? B is user input ? You are trying to return both positive and negative H that is as near 2000 as possible?

Please see the attached. if variable is B then in this case B is 2000 and the peak is 10000. So we need to return the cell position where we see +B on the increasing side to 10000 and where we see +B on the decreasing side from 10000. The two points with arrow are what I want to find the cell location for.

As you can see, we don't exactly see a value of 2000 but only values that are close. The closest value is the one I would like to find

Workbook1.xlsx

Thanks for the help. One thing about this is that I want to put this into a VBA macro so that I can get row numbers out of it and use another column (not listed) to calculate further information

Thanks for the help. I am still trying to figure out exactly how the code works for this but I appreciate it.

Could you also help me with the further operation of this? The values also go in the negative direction as well in a similar form. They start as negative numbers near zero then decrease to a min negative number ~ -10000 then increase back to zero

See the attached for a more realistic data set. FYI the max we will use is typically a fixed value (10500) or (-10500) and looking for a value (2000) or (-2000)

workBook1.xlsx