Source
r/excel•
gezza07

Find closest value in a cell range (No Arrays!)

Waiting on OP

Hi All,

I need help finding creating a formula the returns the closest number in a range in.

For example, I type 12 against a list of {10,15,25} and it would return 12. If i type in 13, it would return 15. I have found a solution using array formulas but would like to avoid that if possible.

Posted Aug 6, 2014, 10:49 PM UTC

06
bgritzut10•

I type 12 against a list of {10,15,25} and it would return 12

I'm assuming you meant to say it should return 10?

  • {10,15,25} is in cells A1:A3
  • lookup number (in your example, 12) is in cell B1
  • List in column A needs to be in ascending order
  • Formula below works where the input is in the range (minimum in column A, i.e. 10, to max-1 of column A, i.e. 24). Add if() statements to account for those inputs < 10 and >= 25.

The formula I use is:

=IF(ABS(B1-INDEX(A1:A3,MATCH(B1,A1:A3,1)))<ABS(INDEX(A1:A3,MATCH(B1,A1:A3,1)+1)-B1),INDEX(A1:A3,MATCH(B1,A1:A3,1)),INDEX(A1:A3,MATCH(B1,A1:A3,1)+1))

You will need to add in the lower and upper bound conditions with if statements yourself. I deliberately did not include it to make the core of the formula a little clearer.

1
gwo10•

This doesn't work when the value is less than 10 and greater than 25 in your example btw.

1
bgritzut10•

I know, I mentioned that in my 4th bullet point. He/she just needs to add 2 if() statements in front. I left it out to make the core of the formula more clear, it's a little messy already.

2
[deleted]•

[deleted]

1
gezza07OP•

Solution Verified

1
Clippy_Office_AsstMOD•

You have awarded one point to bgritzut

1
Clippy_Office_AsstMOD•

Hi!

It looks like you are trying to ask a question! Since you have not responded in the last 24 hours, I am here to assist you!

If your questions has been solved, please be sure to update the flair.

Would you like help?

Help Changing Your Flair?

Flair Descriptions

1