In this tutorial, we’re going to explore the powerful combination of IF and VLOOKUP functions to calculate sales commission rates. The basic premise of this worksheet is to report calculations for those that made their sales goal and column. We will also be looking at whether or not it’s an A, B, or C group and how that affects the commission rate.
Let’s start with the VLOOKUP formula, which can be found in the formulas tab under lookup and reference. VLOOKUP is a very powerful function that looks up a value in the leftmost column of a table. In this case, our lookup value would be E – representing our table array. We need to hit F4 to make this an absolute reference so that the dollar signs go around the columns and numbers to lock it down. We want to make sure that’s there or we make it into a table. The table range is the whole dataset.
Next, we’re going to type number two, and we want to get an exact match, so we’ll type false or a zero there. If we copy this down, we’ll see all those values there. If we change the letter A to a B, we can see that the formula went down and found it. If there is something in there like the letter F which doesn’t exist, VLOOKUP will return N/A because it couldn’t find what it was looking for.
We can also make this a table by selecting the group of data, going to the insert tab, and clicking table. We can name our table Commission or Comm, which we’ll be able to use in the function. This time, if we do our VLOOKUP, we can look up the value D2 in our Commission table, which is absolutely referenced so we don’t have to worry about the dollar signs. If the table grows and we have another column like D and E referencing, then it’s going to auto-update as well.
Now let’s move on to the IF function. We go to the logical category and click IF. We’re looking for our test. We only want to run that VLOOKUP on those records where the person sold more than their goal. We’ll test whether or not cell C2 is greater than or equal to D2, their goal. If that’s true, we’ll paste in that VLOOKUP. We can see right now it’s getting that 14%, so we’ll use that to multiply it by in asterisks at the end times C2.
If it’s everybody else who didn’t make their goal, then it’s just going to be no commission, which we’ll denote with the text notation “no commission.” When we click away, it puts quotes around “no commission.” Be aware that you need to put quotes around any text output if the true or false is text. If it’s a number, don’t worry about it.
In conclusion, we’ve learned how to use IF and VLOOKUP functions together to report calculations for those who made their sales goal and column. We’ve explored the powerful combination of these logical functions to calculate commission rates and compared using a table versus an absolute reference. As always, you can check out Steve Chase Docs dot wordpress.com to find more videos and blog post tutorials.