+ 0 Votes VLOOKUP gechurch 1 year ago 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 here2) 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 - $880Now 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.