Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
    
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
up vote 2 down vote accepted

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .