Search
Excel formula to look up the closest number in a range
- Sean O'Shea
- Dec 20, 2020
- 1 min read
You can use an array formula posted here, to find the number which is closest in a range to a given value.
=INDEX(DesignationNumber,MATCH(MIN(ABS(Plaintiff-E2)),ABS(Plaintiff-E2),0))
Be sure to press CTRL + SHIFT + ENTER when inputting this formula.
In this example, the formula searches for the values in cell E2, the range from B2:B8, and returns the corresponding number listed in A2:A8. The formula works with named ranges. You can set up a named range under Formulas . . . Name Manager:
When setting the named ranges for the formula, select only the numbers in the ranges, not the column headings, or the formula will not work.
The value returned is that in the named range entered after the INDEX part of the formula. The range to search in is that named after MATCH in the two ABS parts which return an absolute value of a calculation - the number not accounting for whether it is positive or negative.
