MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Nearest number to a match

Posted by Don on July 18, 2000 5:16 AM

Using Excel or VBA, does anyone know how to find the nearest number to a match in a list of numbers? Example; The number 41.166245 is calculated and the list contains the number 41.1875, the nearest match. How do I get the computer to tell me that?

Posted by AB on July 18, 0100 6:39 AM

I have a "Lookup.xls" example file you might find interesting. In particular, one of the sheets is labeled "NotSoSimple Lookups". It shows how to find the closest value and then gives two options for a split: default to lower or default to higher number.

It also provides the solution in two forms: standard cell logic and a VBA custom function (CLOSEST). Stephen Bullen helped me out with the cell logic solution on this one. It's worth a peek.

-Aaron ;)The Excel Logic Page