Questions

Answer for:

EXCEL FORMULA HELP

Message 2 of 2

View entire thread
+
0 Votes
gechurch

You want to use the VLOOKUP function. Start by creating two columns somewhere (I'm going to use columns A and B of Sheet2). In column A add your values (70, 75, 80 etc). In column B add the corresponding commission amount (700, 750, 880 etc). This is your lookup table.

Now go back to Sheet1. Enter your actual sales numbers into a column (I'm going with column E, just to be different). In column F insert a formula and select VLOOKUP. You'll need to enter four things:
1) The lookup value. Enter E1 here
2) The lookup table. Enter Sheet2!A1:B23 here.
3) The column offset in your lookup table. You want to select column 2 (I'll explain why below)
4) Range lookup. You'll probably want to set this to true.

What this does is:
* Takes the value (E1)
* Goes and looks for that value in the first column of your lookup table (ie. column A of sheet2)
* When it finds it, it then looks on the same row for the value to return. You chose 2, which means it will return the value from column B of sheet2.

So if you enter 80 into cell E1, the lookup will go and find 80 in Sheet2!A3. It will then return the value from column B - $880

Now let's say you enter 79 into cell E1. If you set range lookup to false the lookup will fail because 79 does not exist in column A of Sheet2. If you set range lookup to false however it will go with the closest match it can find. The closest value in your lookup table is 75, so $750 will be returned.