# Quicker alternative to IF function for multiple variables? (excel 2010)

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?

-

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

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

-