Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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


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

share|improve this question
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
You probably want to make the lookup table row references absolute - C$1:D$4 - or t'll break when you auto-fill the formula to cells below – pelms Nov 13 '15 at 12:23

Keep what you had. Just use the formula below:


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


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.