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.

Step 5: After clicking on Step In button we get the lookup value.

Step 6: Click on Step Out

Step 7: Click on Evaluate

Step 8:IF condition gets executed (B2<5).

Step 9:Here value is 8 in B2

Step 10: Hence it reurns False

Step 11:  Hence it looks for Table B

Step 12: Result (8.25%)

Scope of Usage:

ü  Should  be used with minimal conditions

ü  Not recommended to be used where IF statement is going to be rewritten

ü  Can be used in cases where co-relation between values are less likely to change

ü  Can be used in cases which will require less maintenance i.e. commission rate once defined is less probable to be changed

ü  Can be used with multiple tables

ü  Can be used to lookup with criteria i.e. different processing rules

ü  Can be used to find approximate match or exact match

ü  Can be used to define the table when the condition is met

ü  Can be used to define the table when the condition is not met

ü  Can be used to identify the data range