How To Use Vlookup With If Statement Step By Step Examples

VLookup with IF Statement in Excel

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.

VLOOKUP with IF statement: Returns “True/False” or “Yes/No.”’

We will briefly explain the IF statement and ‘Vlookup’ Function, so that combination becomes easier to explain. ‘IF’ is used when you want a condition to decide which value will be populated in a cell.

In the above formula, “logical_test” is the condition we are testing, the value if the condition is “True,” and then the value if the condition is “False.”

Below is an example:

Similarly, for the VLOOKUP function, suppose you have data in a table. You want to look up a value in any of the columns corresponding to a value in the table’s left column.

Suppose cells B2:E6 is the data containing students’ marks in the 3 subjects shown. For example, suppose you want to know Vijay’s marks in Chemistry.

From the VLOOKUP formula template above, you can see the lookup_value is “Vijay,” table array is “B2:E6” since we are interested in marks of “Chemistry,” column number is 3. Since we are interested in an “exact match,” the fourth argument is “FALSE,” which denotes an approximate match.

Now that we have revised these 2. Let us investigate the combinations of these 2.

The generic formula will be:

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

Typical use cases for these include:

Compare the value returned by VLOOKUP with a sample value and return “True/False,” “Yes/No,” or 1 out of 2 values we determined.Compare the value returned by VLOOKUP with a value present in another cell and return values as above.Compare the value returned by VLOOKUP. Then, based on it, choose between two sets of calculations.

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.

Vlookup With IF Function 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.”

Vlookup With IF Function 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:

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.

Vlookup With IF Function Example #3

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

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 excelData Validation In ExcelThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more 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.

Vlookup With IF Statement 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 ExcelISNA Function In ExcelThe ISNA function is an error handling function in Excel. It helps to find out whether any cell has a “#N/A error” or not. This function returns the value “true” if “#N/A error” is identified or “false” if not identified.read more .

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.

Things to Remember About Excel Vlookup with IF Function

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. So, spend your time understanding the formulas and practice them.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.

You can Download these Vlookup with IF Statement Excel Template – Vlookup with IF Excel Template

VLookup with IF Statement in Excel#

How to Use Vlookup with IF Statement in Excel?#

Vlookup With IF Function Example #1#

Vlookup With IF Function Example #2#

Vlookup With IF Function Example #3#

Vlookup With IF Statement Example #4#

Things to Remember About Excel Vlookup with IF Function#

Recommended Articles#