2

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?

2 Answers 2

2

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
2
  • Ahh.. totally forgot about VLOOKUP! Such a powerful function I always overlook! Thanks!
    – Aasim Azam
    Aug 17, 2012 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, 2015 at 12:23
0

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.

You must log in to answer this question.

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