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
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


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 .