Find closest number in list

[nancymk]

Let's say you have one of those M&M contests, where a bunch of people guess the number of M&Ms in a jar. In column B I have all the guesses. In cell A1 I have the right answer and in cell A2 I want a formula that will search the guesses in column B and give me the number closest to A1.

This is not for an M&M contest, but same concept CSE formulas welcome.

Thanks!

 

[Simon Lloyd]

use vlookup with the last parameter as true, this way it will find the nearest match without exceeding the the figure its looking for:
=Vlookup(D1,A1:A1000,1,True)

 

[nancymk]

Thanks, though it can actually exceed it. Just needs to be closest.

 

[nancymk]

I just realized this could result in two numbers being right, so instead if the formula could show the smallest difference by absolute value.

 

[Temple77]

I just realized this could result in two numbers being right, so instead if the formula could show the smallest difference by absolute value.

=abs(guess-answer) then either sort or use rank() w/ and look for one.

 

[Simon Lloyd]

What do you mean?, do you have the numbers sorted in ascending order? do you see any duplicates?

A good example of finding duplicates in a column can be found here http://www.mrexcel.com/forum/showthread.php?t=69659

 

[Domenic]

Let's assume that A2:A100 contains the data/guesses, and C2 contains the target number, try...

=INDEX(A2:A100,MATCH(MIN(ABS(A2:A100-C2)),ABS(A2:A100-C2),0))

...confirmed with CONTROL+SHIFT+ENTER.

 

[nancymk]

I know how to find the answer. I was just looking for a fancy formula to populate it for me. So if the answer in A1 changes or the dataset changes it would automatically show me the new answer.

 

[nancymk]

Thanks Domenic!! Just saw your reply. Works perfectly!!

 

[nancymk]

FYI - I just added minus C2 to the end so I could get the difference. Then in another column it looks for winners based on that difference either up or down.