IF VLOOKUP Formula in Excel (Table of Contents)
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, then 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 source 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 IF VLOOKUP Formula in Excel?
IF VLOOKUP Formula in Excel is very simple and easy. Let’s understand how to use the IF VLOOKUP Formula in Excel with some examples.
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 need to find whether the specified product is in stock or not.
Please select the Item and paste it in a cell for which we want to check whether the item is in stock or not.
Select the cell F2 where you want the get 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”; that is, the stock is in hand. Else it will return “No”, which means the stock is not in hand.
Here we have checked the item “tobacco_barn” is in stock or not. 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 done for a period. We need to find if there any maximum sales for a specified item.
Select the 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, is there any maximum sales that occurred or not.
Select cell E2 and apply the formula.
The formula applied with a logical operation by referring to a cell value in E2. For the product given in E2 is 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.
- According to the result, the true section or false section will be returned as the final output.
- 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 be used to perform different calculations based on given criteria.
- IF Vlookup is used to perform different calculations according to the Vlookup condition.
- More than one Vlookup function can be associated with the IF function.
- Instead of using texts, the Vlookup function is performed in the true and false section.
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 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 satisfies, then the true section of the IF function will be performed: VLOOKUP (D2, A2: C15,3) *20%; it returns the 20% of the sales done for the specified person.
When the IF condition does not satisfy VLOOKUP (D2, A2: C15,3) *10%, this formula will be executed. 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 the 20% of the sales amount done by salesperson Alwin will be returned as the final output that is 10.
Things to Remember About IF VLOOKUP Formula in Excel
- Should be careful about selecting the range where you want to apply the IF(Vlookup(..)) function.
- To avoid mismatches while copying the formula, use the $ symbol along with the range value.
- IF with Vlookup function is used when you want to take any decision based on the result.
- Along with the IF function, any text message can be given in the true and false sections.
Recommended Articles
This is a guide to IF VLOOKUP Formula in Excel. Here we discuss How to use the IF VLOOKUP Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
