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 the following information in a table

      C           D
   ----------- ----------- 
1 |  25%      |   1       |
   ----------- ----------- 
2 |  50%      |   2       |
   ----------- ----------- 
3 |  75%      |   3       |
   ----------- -----------
4 |  100%     |   4       |
   ----------- -----------

If a user inputs into a cell (A1) any of the values in column D, cell (B1) will display the number in column C.

I am using the following function to acheive this

=IF(A1=D1,C1,IF(A1=D2,C2,IF(A1=D3,C3,IF(A1=D4,C4))))

Is there a shorter way to do this using a different function?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

What you want is the VLOOKUP function. First, you need to reverse columns C and D so that your "lookup" column is in C, and the values to return are in column D. Then, in cell B1, use the following formula:

=VLOOKUP(A1, C1:D4, 2)

Changing the value in cell A1 between 1 and 4 should return the proper percentage. For example, if you place the above formula in cells B1 and B2, and fill in the constants for all other cells, you should see:

  |  A     B     C     D
  -----------------------
1 |  4   100     1    25
2 |  1    25     2    50
3 |              3    75
4 |              4   100
share|improve this answer
    
Ahh.. totally forgot about VLOOKUP! Such a powerful function I always overlook! Thanks! –  Aasim Azam Aug 17 '12 at 21:19

Keep what you had. Just use the formula below:

=INDEX(C1:D4,SMALL(D1:D4,A1),1)

Short, simple, and no need to change your format. Index is a vlookup/hlookup function (vlookup is automatic and index is the stick in a car). Small looks at a list and the 2nd parameter looks at which rank you put, i.e. "1","2","3", etc.

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.