VLookup with IF Statement

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Nannila Jai Ratna

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

How To Use Vlookup With IF Statement In Excel?

Now that we have explained the formula template and some of the use cases, let us try to explain it better through examples and then present it lucidly.

Examples

Example #1

The data table remains the same, as explained during the VLOOKUP function.

Vlookup with If Example 1

Now, let us suppose we have decided on a condition that if the marks scored are greater than 92, then it will be shown as “Great.” Otherwise, it will be shown as “Good.” When we see the marks, we are not interested in their actual marks, but we want to know whether they are great or good.

Now, here we are applying the formula.

Vlookup with If Example 1-1

Now, see cell F3. We are doing a VLOOKUP like above, which will give the result “92”. Adding that “IF” condition on top of it. Now, it checks if these marks are greater than 92, it is “Great”; otherwise, it is “Good.” Since here, we are getting the marks of Vijay corresponding to Chemistry, 92. Hence, the result shown is “Good.”

Vlookup with If Example 1-2

Example #2

Let us move on to another example: where you want to make this cut-off dynamic. You want to change cut-off values and instantly want to see whether the value is “Great/Good” (in this case).

Please see the screenshot below:

Vlookup with If Example 2

Here, please see the formula in cell F5. It is like what we had shown in the earlier example. The only difference is that the value to which now you are comparing the results is a dynamic one stored in cell E8.

Vlookup with If Example 2-1

As we reduced the cut-off to 90, Vijay’s performance in the same Chemistry subject has been classified as “Great” compared to “Good,” as shown in the previous example.

Example #3

Let us move to the third example. Now, wherein, based on the VLOOKUP result. It performs a calculation.

Now, let us use different data this time. Suppose we want a discounting strategy based on the retail price of stuff.

Please see the screenshot below for the data:

Vlookup with If Example 3

Cells B3:C8 shows the price of “Fruits.” We have used Data Validation in excel so that in cell E5, you can select any of the fruits mentioned in column B.

Now, let us move to our pricing strategy, i.e., if the cost is more than 180, we will provide a 20% discount. Else, we will give only a 10% discount.

We implement this by using the formula as shown in the screenshot below:

Vlookup with If Example 3-1

For example, see the formula in cell F5. First, it checks the result of the LOOKUP function. If it is greater than 180, then we multiply the value by 80% (20% discount). Else, we multiply the result by 90% (10% discount).

Vlookup with If Example 3-2

Now, let us move on to the final example.

Example #4

Let us use the above data only. Suppose you want to see whether the fruit is present in the list or not. It will provide an example of Excel’s combination of IF statement, VLOOKUP, and ISNA function in Excel.

Suppose you did a VLOOKUP for the price of “WaterMelon.” Since it is not in the list, this will give you a #NA error.

Vlookup with If Example 4

See cell B11. The formula is shown in the taskbar. Now, we will show you the formula to deal with such cases. Our idea is that if the fruit we searched for is not present, it should give the result “Not Present.” Else, it should return the price of the fruit.

Applying the formula of IF statement, ISNA, and VLOOKUP.

Vlookup with If Example 4-1

Just put the name of the fruit in cell E2. Then, cell C11 will give you the result you are looking for. Cell C11 will give “Not Present” if the fruit is not present” as you can see for “WaterMelon.” Else, it will give the price as shown for “Apple.”

Vlookup with If Example 4-2

We hope these examples give you all the clarification. Please practice for a better and more advanced understanding.

Important Things To Note

  • VLOOKUP function vertically looks for a particular value.
  • Whereas, IF function returns the conditional statement.
  • The VLOOKUP with IF statement helps to look for a vertical value with a conditional TRUE or FALSE statement.

Frequently Asked Questions

1. What is VLOOKUP with IF statement?

VLOOKUP is vertical lookup and is used to look for a particular value vertically. IF is used when we want a condition to decide which value will be populated in a cell.

The VLOOKUP with IF statement’s generic formula is:

IF(VLOOKUP(…) = sample_value, TRUE, FALSE)

2. Example of VLOOKUP with IF statement.

Consider the following example. The table shows the quarterly ratings of different samples A, B, C, D, and E. Now, let us use VLOOKUP with IF statement to find whether sample pear’s rating is more than 7.

Vlookup with IF Statement FAQ 2

Now, let us enter the formula, =IF(VLOOKUP("Pear",$A$1:$D$6,3,FALSE)>7,"Good","Best").

Press Enter key.

Vlookup with IF Statement FAQ 2 - Output

We can see the result as Best, indicating the fact that sample pear’s rating is more than 7.

Likewise, we can use VLOOKUP with IF statement.

3. What are the uses of VLOOKUP with IF statement?

The typical uses of VLOOKUP with IF statement are to compare:
• The value returned by VLOOKUP with a sample value and return “True/False,” “Yes/No,” or 1 out of 2 values we determined.
• The value returned by VLOOKUP with a value present in another cell and return values as above.
• The value returned by VLOOKUP. Then, based on it, choose between two sets of calculations.

Download Template

This article must be helpful to understand the Vlookup with If Condition in Excel, with its formula and examples. You can download the template here to use it instantly.

Vlookup with IF Excel Template