Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 1 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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