This particular example will make more sense to you if you have some accounting knowledge (Taxation to be precise). VLOOKUP EXAMPLE 2: Income Tax rate Calculator And since the table doesn’t have an exact match for this search, it retrieved the commission rate for the largest value that is less than the lookup value, (which is $40,000).Īfter you finished writing the formula in the first cell, you can use the fill handle to copy the formula to the rest of the cells. For instance, in the example above, the formula in cell G4 searched to find $46,356 in the first column of the lookup table.
VLOOKUP approximate match will search for the largest value that is less than the lookup value. The final argument is set to TRUE which means “approximate match”.The third argument specifies the column in the table from which to pull the commission rate.However, if you still want to use a normal range and not a named range, ensure to make it an absolute reference – like this: $B$4:$C$14. For instance, using B4:C14 is the same range as the commision_Schedule. The table array argument can either be a named range or just a normal range. The second argument is also referring to the table from which to pull the commission rate.The first argument referred to the cell that contains the value to look for.The example below shows a worksheet that uses the VLOOKUP function to determine the commission a sales rep gets, based on how much sales she makes. The commission can be set on flat rate, which is usually a percentage, say 7 percent, on any sales the representative makes.īusinesses can also use progressive commission, where the percentage of commission rises as sales rep hit certain targets.įor example, selling $10,000 worth of goods or services can earn the representative 10 percent commission, a 15 percent commission on the next $10,000, and so on. The more sales they make, the more commission they earn, and the more money the company makes too. The commission of such workers is often calculated as a percentage of the sales they made. Many sales jobs pay workers on a commission basis. VLOOKUP EXAMPLE 10: Partial text match with wildcard.EXAMPLE 9: VLOOKUP from another Workbook.EXAMPLE 3: Return products and prices using ID.VLOOKUP EXAMPLE 2: Income Tax rate Calculator.VLOOKUP EXAMPLE 1: Commission Calculator.
0 kommentar(er)
