Closest Value without an array formula

Hi, I'm having trouble figuring out how to find the closest value without an array formula.

I assume there's a helper column involved.

Anybody know the basic logic? must be back to Vlookup of an average or something...

In this example we find the value in column A that is closest to the value in D1. We use column B as the "helper column"

nice! thanks, there goes my last CSE formula! Hey, in terms of speed, how big would a helper column have to be to lag like an array formula?

Originally Posted by stockgoblin42

in terms of speed, how big would a helper column have to be to lag like an array formula?

There is calculation timer code here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Then you can do the tests and see for yourself.

The helper column is usually pretty quick.