3

I have a column (A) containing numbers between 0-200. By comparing the value of A against the values of C, I want to pick the nearest cell and thereby return the value of D.

So, if A is 160 I want to get C4/D4 below:

  C       D
1 69      20
2 144     30
3 155     60
4 163     70
5 171     100
6 176     120
7 189     140
  • How do you want to compare A to C? What values do A and C have? – Brad Patton Mar 1 '13 at 15:06
  • 1
    Ok re-reading the question I think I get it. For a given value you want to find the closest match in the C column. – Brad Patton Mar 1 '13 at 15:12
  • Yep. Sorry if that wasn't so clear. So when I found the closest match in the C column, I should be able to pick out the value in the D column – Industrial Mar 1 '13 at 15:18
2

If it's actually the closest value required (and not next highest or lowest) then try this "array formula" in Excel assuming lookup value in A1

=INDEX(D1:D7,MATCH(MIN(ABS(C1:C7-A1)),ABS(C1:C7-A1),0))

confirmed with CTRL+SHIFT+ENTER = if A1 is 160 that returns 70, if A1 is 158 you'll get 60

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.