# VLookup with IF Statement

Published on :

21 Aug, 2024

Blog Author :

Wallstreetmojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya

## What Is VLOOKUP With IF Statement In Excel?

VLOOKUP is a reference function, while IF is a conditional statement in Excel. Both functions are combined to find a certain value that meets the criteria and matches the reference value. Then, based on the results of the VLOOKUP function, IF statements display the result. In other words, we nest VLOOKUP in the IF function.

We will combine the twin functions of the IF function and VLOOKUP. We will also see how to deal with #NA errors, which we might sometimes receive while using a combination of IF statement and VLOOKUP. While the two are quite important on their own, they provide more value.

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 E’s rating is more than 5.

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

Press Enter key.

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

Likewise, we can use VLOOKUP with IF statement.

• VLOOKUP with IF statement is a combination formula used to lookup a value with IF function’s conditional statement.
• For VLOOKUP to work, the lookup_value should always be in the leftmost column of the data table, which you will be giving the input in the VLOOKUP formula.
• We can use the combination of IF statement and VLOOKUP for error handling, which will form a significant part when you build dashboards, monthly planners, etc.
• When you are doing VLOOKUP, normally go for “Exact Match” as the fourth argument of VLOOKUP when you are interested in matching the lookup_value exactly.

### 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.

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.

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.”

#### 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).

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.

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:

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:

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).

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.

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.

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.”

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.

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.

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

Press Enter key.

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.