Vlookup is a reference function while if is a conditional statement in excel, these both functions are combined together to find out certain value which meets the criteria and also match the reference value, based on the results of Vlookup function If statements display the result, in other words, we nest Vlookup in If function.
VLookup with IF Statement in Excel
Here, we will combine the twin functions of ‘IF Function’ & ‘VLOOKUP.’ We will also see how to deal with #NA errors, which we might sometimes receive while using a combination of ‘IF Statement’ & ‘VLOOKUP.’ While the two are quite important on their own, together, they provide more value.
‘Vlookup’ with ‘If’ statement: Returns ‘True/False’ or ‘Yes/No.’
I will briefly explain to you the ‘IF Statement’ & ‘Vlookup’ Function, so that combination becomes easier to explain. ‘If’ is used when you want to have a condition decide which value is to be populated in a cell.
In the above formula, “logical_test” is the condition on which we are testing, then the value if the condition is True, and then Value if the condition is False.
Below is an example:
Similarly, for the ‘Vlookup’ Function, suppose you have data in a table, and you want to look up a value in any of the columns corresponding to a value in the left column of the table.
Below is an example:
Suppose, cells ‘B2:E6’ is the data containing the marks of Students in the 3 subjects shown. Let’s say you want to know the marks of Vijay 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, and since we are interested in an “exact match,” 4th argument is “FALSE” which denotes approximate match.
Now that we have revised these 2 let’s 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 that of a sample value and return “True/False,” “Yes/No,” or 1 out of 2 values determined by us.
- Compare the value returned by Vlookup with a value present in another cell and return values as above.
- Compare the value returned by Vlookup and, based on it, choose between 2 sets of calculations.
How to Use Vlookup with IF Statement in Excel?
Now that I have explained to you the formula template and some of the use cases, let’s try to explain it better through examples and then explaining it in a lucid way.
Vlookup With IF Function Example #1
The data table remains the same, as explained during the ‘vlookup’ function.
Now, let’s suppose we have decided on a condition, that if the marks scored is greater than 92, then it will be shown as “Great”; otherwise, it will be shown as “Good.” Now, when I see the marks, I am not interested in their actual marks, but I just want to see whether they are great or good.
Now here we are applying the Formula.
Now, see the cell F3, we are doing a ‘vlookup’ just like above, it 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, i.e., 92. Hence the result shown is “Good.”
Vlookup With IF Function Example #2
Now, let’s move on to another example, say where you want to make this cutoff dynamic. You want to change cutoff 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 I 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.
Now, see as the cut off was reduced to 90, the performance of Vijay in the same subject of Chemistry has been classified as “Great” compared to “Good” as shown in the previous example.
Vlookup With IF Function Example #3
Let’s move to the third example now, wherein, based on the ‘vlookup’ result. It performs a calculation.
Let’s use a different data this time. Let’s suppose we want to have a discounting strategy applied based on the retail price of stuff.
Please see the screenshot below for the data:
Cells B3:C8 shows the price of Fruits. I have used the Data Validation in excelData Validation In ExcelData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs. so that in cell E5, you can select any of the fruits mentioned in column B.
Now, let’s move to our pricing strategy, i.e., if the cost is more than 180, we will provide a 20% discount, else we will provide only a 10% discount.
We implement this by using the formula as shown in the screenshot below:
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% (i.e., 20% discount), else we multiply the result by 90% (i.e., 10% discount).
Now, let’s move on to the final example.
Vlookup With IF Statement Example #4
Let’s use the above data only. Suppose you want to see whether the fruit is present in the list or not. This will provide us with an example where we can use the combination of IF statement, VLOOKUP & 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. .
Suppose you did a ‘vlookup’ for the price of ‘WaterMelon.’ Since it is not present in the list, this will give you a ‘#NA’ error.
See cell B11. The formula is shown in the taskbar. Now, to deal with such cases, we will show you the formula. Now, our idea is that if the “Fruit” we searched for is not there, then 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, and cell C11 will give you the result you are looking for. If the fruit is not present, cell C11 will give “Not Present” as you can see for “WaterMelon.” Else, it will give the price as shown for “Apple.”
I hope these examples give you all the clarification. Please practice for a better and 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.
- Combination of “If Statement” & “Vlookup” can be used for error handling, which will form a very significant part when you are building dashboards, monthly planner, etc. So, spend your time in understanding the formulas & 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
This has been a guide to Vlookup with IF Condition in Excel. Here we discuss Vlookup Function with IF Statement in Excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –