Introduction to VLOOKUP Formula in Excel
Using Vlookup along with IF Function in Excel is used to get conditional results on the basis of compared values. When we use the IF function with Vlookup, the IF statement compares the result obtained from the vlookup statement with the selected value. An easy way to apply Vlookup with IF is to apply the Vlookup function with the complete range and then use this syntax with IF Statement. We can have any statement or output if the value obtained from vlookup comes TRUE or FALSE. And always quote the final statement, which we want in inverted commas.
Syntax
Arguments
- Lookup value – This refers to a cell value or a text that you are looking for
- Table_ array – Defines the range of cells or sources where you want to look for the value.
- Col_index_number – Number of the column from which you want to return the value.
- Range_lookup – True or false to get the exact match or similar data.
How to Use the IF VLOOKUP Formula in Excel?
IF VLOOKUP Formula in Excel is very simple and easy. With some examples, let’s understand how to use the IF VLOOKUP Formula in Excel.
Example #1 – Using the IF Function with Vlookup
Below is an example of using the “IF” function with Vlookup. The lookup value is compared with an integer value. Where the list of items and their quantity is given, from the given list, we need to find whether the specified product is in stock or not.
Please select the Item and paste it into a cell for which we want to check whether it is in stock.
Select the cell F2 where you want the final result and apply the formula IF VLOOKUP.
The function Vlookup is applied as VLOOKUP (E2, $B$2: $C$15,2, FALSE) to find the quantity of the item name given in F2.
And using “IF”, this Vlookup result will be compared with “0”.
- If the quantity is not equal to zero, it will return “Yes“; the stock is in hand. Otherwise, it will return “No”, meaning the stock is not in hand.
Here we have checked whether the item “tobacco_barn” is in stock. Since the Quantity is not zero, it returned the value “yes”.
Example #2 – Compare the Vlookup Value with Another Cell Value
Let’s look at an example to compare the Vlookup value with another cell value.
The above given are the different products and their amount of sales that have been done for a period. We need to find out if there are any maximum sales for a specified item.
Select cell C2 and apply the formula, By using the MAX Formula.
The result value will be pasted to cell E2. And is the highest amount of sales done within the given data.
To check the product given in D2, are there any maximum sales that occurred or not.
Select cell E2 and apply the formula.
The formula is applied logically by referring to a cell value in E2. For the product given in E2, the maximum sales happened. So the result of the applied IF function is “Yes”.
- IF function will perform a logical operation with the Vlookup value and return a Boolean value.
- The final output of the IF statement will depend on whether the result is true or false.
- Instead of true or false, you can mention any text in both sections.
Example #3 – IF Vlookup Formula to perform Multiple Calculations
Apart from displaying the text messages given, this formula combination can perform different calculations based on given criteria.
- You can use IF with Vlookup to perform various calculations based on the Vlookup condition.
- You can associate multiple Vlookup functions with the IF function.
- Instead of using text, you can utilize the Vlookup function in the true and false sections of the IF statement.
It gives the products and the amount of sales done by different sellers. Here we need to find the commission for the seller specified in cell D2 according to the sales they have completed.
If the sales amount is greater than $50, then a 20% commission will be provided else, 10%. So these two conditions are specified in the given formula.
The IF function is performed on this result by applying a logical operation “>=50”.
If this condition is satisfied, then the true section of the IF function will be performed: VLOOKUP (D2, A2: C15,3) *20%; it returns 20% of the sales done for the specified person.
This formula will be executed when the IF condition does not satisfy VLOOKUP (D2, A2: C15,3) *10%. And it will return 10% of the sales amount.
Here the first condition satisfies and returns 20% of the sales value of the specified person.
So 20% of the sales amount done by salesperson Alwin will be returned as the final output, 10.
Things to Remember About IF VLOOKUP Formula in Excel
- Be careful about selecting the range where you want to apply the IF(Vlookup(..)) function.
- Using the $ symbol and the range value to avoid mismatches while copying the formula.
- You can use the Vlookup function with an IF statement to decide based on the result.
- You can include any text message in the true and false sections of the IF function.
Recommended Articles
This is a guide to the IF VLOOKUP Formula in Excel. Here we discuss How to use the IF VLOOKUP Formula in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
