Retrieving closest match

  • Thread starter Thread starter jack
  • Start date Start date
J

jack

Can someone give me some guidance on how I can retrieve the closest value in
a column to a target number, whether it be larger or smaller than the target
number. VLOOKUP doesn't seem to work for what I am trying to accomplish.
The problem is analogous to a series of guesses for the number of jellybeans
in a jar, determining what is the closest guess to the actual number and
return the winner's name.
 
One way...

Assume:
A1:A10 are names
B1:B10 are numeric values (that persons guess)
D1 = lookup value (some number)

Try this array formula** :

=INDEX(A1:A10,MATCH(MIN(ABS(B1:B10-D1)),ABS(B1:B10-D1),0))

Note that if there is more than 1 "closest" the formula will return the
*1st* closest it finds from top to bottom. For example:

Lookup value = 100

Joe...88
Tom...110
Sue...65
Lee...90

Both Tom and Lee are the closest but Tom is the 1st closest.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Thanks for the suggestion, I'll give it a try. BTW..... I'm still using
your check register. Thanks again for that.
Jack
 
Back
Top