Subject Line

You will enjoy using VLOOKUP with IF statement.

Overview

Sometimes you may need to look up data or values from multiple tables on the basis of some conditions using IF statements which are incredibly robust. In this case we use Vlookup along with IF statement to search the value as per your criteria. There could be possibilities of the conditions being recorded separately also; so the lookup criteria which would make your search operation slightly complex. Therefore, in order to simplify the search operation you can combine the ranges you wish to use for your conditions but only in case where you need to make logical comparison using minimal conditions you can make use of Vlookup along with IF statement.

Working

Let us understand the working with the same employee information (Figure 1: Employee Information) we have been using. The below is the employee listing which also contains the list of Sales representatives for the company.

Figure 1: Employee Information

The employees have serviced for a particular number of years i.e. tenure. Its appraisal time and the management would want to work out the commission or incentives to be rewarded to the sales representatives of the company.

In the example we want to find the commission rate and the commission based on Sales amount i.e. the volume of Sales done by each employee and tenure in years i.e. the period for which the employee has been working with the company

Below (Figure 2: Criteria and Calculations);

Table A represents the Sales figures

Table B represents the applicable commission rate to be applied (for tenure less than 5 years)

Table C represents the applicable commission rate to be applied (for tenure more than 5 years)

Figure 2: Criteria and Calculations

Note: There are four employees (highlighted in yellow who are part of the Sales workforce for the company)

The other employees are part of the marketing teams and are being rewarded for the Sales recognized by the company due to their efforts on campaigns and marketing initiatives.

Step 1: We will use Vlookup along with IF statement for finding the Commision Rate.

Below is the formula-

=VLOOKUP(C2,IF(B2<5,$H$3:$I$9,$K$3:$L$8),2,1)

Explanation to the commission rate formula

We want commission rate as per sales amount and as per tenure hence we use IF statement and if the tenure is <5 than Table B will become the Table array else it will take Table C and lookup 2^nd column.

In simple words,

=IF (Something is true, then do something, otherwise do something else)

Note that we have mentioned 1 i.e. true which is approximate match.

=VLOOKUP(C2,IF(B2<5,$H$3:$I$9,$K$3:$L$8),2,1)

Step 2: Select the cell D2 to D11 and drag the formula using Ctrl+D.

Step 3: Now we require Commission for each sales representative.

Formula for Commission is Sales * Commission rate (i.e. Column C X Column D)

Understanding how the formula works with the help of Evaluate Formula

Step 1: Select the cell (D2 in this case) that you want to evaluate. Only one cell can be evaluated at a time.

Step 2: On the Formulas tab click on Evaluate Formula in Formula Auditing group

Step 3:Evaluate Formula dialog box will appear.

Step 4: Click Evaluate to examine the value of the underlined reference. The result of the evaluation is shown in italics.

If the underlined part of the formula is a reference to another formula, click Step In to display the other formula in the Evaluation box. Click Step Out to go back to the previous cell and formula.