Did you know that you can use Excel IF statements along with VLOOKUPs?
For example, if you wanted to enter a value from a table into a cell, based on what was in another cell, you could start with an IF statement and then enter the VLOOKUP in the “value if true” part of the IF statement.
Consider this scenario…
Calculate the Commission earned for each CustID. If the Profit Earned is greater than $1,000, look up the Commission % based on the commissions to the right of the data, and multiply the percentage by the Profit Earned. If the Profit Earned is not greater than $1,000, the Commission Earned is 0.
- Select the cell in which you want the results of the IF/VLOOKUP function to appear. Cell I2 in picture above.
- You would need to create an “IF” function first…
- Type =IF(
- First argument — you’ll build the logical test to only get the records where the Profit Earned > $1,000.
- Next argument would be to type the action to be taken if the logical test is true — the results if true depend on the lookup results from the lookup table in Columns K and L, so you’ll need to nest a VLOOKUP function within the IF function to get the Commission %; then multiply that number by Profit Earned.
- Next, type the action to be taken if the logical test is false, in this case, 0 dollars.
- For the formula, you’d need two sets of parentheses, one for the IF function, and one for the VLOOKUP function. Each argument within the functions is separated by commas.
- Here’s what the full formula would look like: IF(H2>1000,VLOOKUP(H2,$K$6:$L$15,2,TRUE)*H2,0)
- The result of the formula is $197.80.
- You could then copy the formula to the range I3:I20 to fill down the column.
Come to Full Circle Computing to learn more about the power of Excel 2016 Functions!!
