How to find the nearest value to a cell in a column?

Question

How to find the nearest value to a cell in a column?

I enter a number in cell B1 and I want to find the closest value to it in the column C (C1:C7) and then return it in cell B2. The numbers in column C are unique and they are in ascending order. I tried the "Lookup" function but it is not accurate since it does not return the closest value to B1. I found a way to do this in Excel with the following formula:

INDEX(C1:C7,MATCH(MIN(ABS(C1:C7−B1)),ABS(C1:C7−B1),-))

However, I can not get it to work with Numbers as it only returns the value in C1 regardless of what the value is in B1 (see below, the formula is in cell B2):

What am I doing wrong? Any help is much appreciated.

iMac (27-inch, Late 2012), macOS Sierra (10.12.2)

Posted on Feb 4, 2017 3:07 PM

Reply

Answer 1

SGIII

Level 8

37,550 points

Feb 4, 2017 7:03 PM in response to htahmasebpour

You're not doing "anything wrong".🙂

The problem is that, unlike Excel, Numbers does not support many so-called "array functions" (functions that can operate on an array stored in memory). Instead Numbers needs many arrays to be laid out in a range of cells. Thus, a close equivalent to your original formula would be this:

The formulas:

B2: =INDEX(C,MATCH(MIN(D),D,0))

D1: =ABS(C1−$B$1)

D2: =ABS(C2−$B$1)

D3: =ABS(C3−$B$1)

D4: =ABS(C4−$B$1)

D5: =ABS(C5−$B$1)

D6: =ABS(C6−$B$1)

D7: =ABS(C7−$B$1)

SG

Reply

Answer 2

Feb 4, 2017 7:02 PM in response to SGIII

Hello SG,

Thank you very much for the quick and clever solution. That is definitely doing the trick! I have switched from Excel to Numbers as I find Numbers to be way more elegant and user friendly. So I have been converting my Excel sheets to Numbers and hopefully Apple will come up with some support for array functions to shine the Numbers even more. Meanwhile, I will use your solution.

Thanks again,

HT

Reply

Answer 3

SGIII

Level 8

37,550 points

Feb 4, 2017 7:07 PM in response to htahmasebpour

Hi HT,

Thanks for the feedback, and green tick!

On array formulas you could tell Apple why they are important to your work by going to Numbers > Provide Numbers Feedback in your menu.

SG

Reply

Answer 4

Feb 4, 2017 9:20 PM in response to SGIII

Sure I will. Thanks for the info.

HT

Reply