Mehedi Hasan Shimul
The IF function and the VLOOKUP function are widely used while working in Excel. The combination of these functions can find out specific values with certain conditions from the dataset. Here, we will discuss 7 ideal examples to use IF statement with VLOOKUP for multiple conditions Range in Excel.
Download Practice Workbook
You can download the practice workbook from here.
7 Ideal Examples of Excel IF Statement with VLOOKUP for Multiple Conditions Range
We can do some marvelous tasks using the combination of IF and VLOOKUP functions. In this section, we will discuss 7 ideal examples of using IF function with VLOOKUP function for multiple conditions range.
1. Use IF Statement with VLOOKUP to Get Yes/No Result
Yes/No results for the presence of any specific value in the dataset can be checked using IF and VLOOKUP functions. Let’s discuss the example stepwise.
- Firstly, write the following formula in Cell F5.
=IF(VLOOKUP(F4,$B$5:$C$9,2,FALSE)=0,"No","Yes")- Consecutively, press Enter.
- As a result, we will see the yes/no result for the presence of Apple (as we inserted Apple in Cell F4) in our stock.
Read More: How to Use IF Statement with Yes or No in Excel (3 Examples)
2. Compare VLOOKUP Result with Another Cell Using IF Statement
In a bit similar way to example 1, we can modify the formula a bit to compare the result with the maximum value in the dataset. Let’s jump to the stepwise procedures.
- Firstly, find the maximum value of column Quantity (Kg) in Cell F4 using the following formula.
=MAX(C5:C9)
- Now, we will compare any other quantity in the dataset with the maximum quantity.
- For that purpose, use the following formula in Cell F6.
=IF(VLOOKUP(F5,$B$5:$C$9,2)=F4,"Yes","No")
Read More: How to Check If Cell Contains One of Several Values in Excel
3. Ignore Error from VLOOKUP and IF Statement Applying ISNA Function
In this example, we have some fruit names in Product and Lookup List columns. We will check if the fruit in the Product column is present in the Lookup List. If the Lookup List doesn’t have the fruit the VLOOKUP function will give error, we can ignore the error using the ISNA function. Follow the given steps for that.
- Firstly, use the following formula in Cell C5.
=IF(ISNA(VLOOKUP(B5,$E$5:$E$7,1,FALSE)),"No","Yes")
Similar Readings
- Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
- How to Use Multiple IF Statements in Excel Data Validation
- Use If Statement Based on Cell Color in Excel (3 Examples)
- Dynamic Data Validation List in Excel with IF Statement Condition
4. Perform Different Calculations on VLOOKUP and IF Statement Result
Now, we will compare the value from the VLOOKUP function with a specific value and if it is greater than or equal to that value, the result will be a discounted value. Otherwise, the formula will return the value from VLOOKUP. Follow the steps.
- Just write the following formula in Cell F5 and press Enter.
=IF(VLOOKUP(F4,$B$5:$C$9,2,FALSE)>=100,VLOOKUP(F4,$B$5:$C$9,2,FALSE)*70%,VLOOKUP(F4,$B$5:$C$9,2,FALSE))
Read More: How to Use IF Statement with Not Equal To Operator in Excel
5. Get Data from Table Based on Another Cell Using VLOOKUP and IF Statement
In this example, we have 2 columns Shop 1 and Shop 2. Based on the value in Cells F4 and G5 we will find the respective Price of fruit. Follow the steps given below.
- Primarily, apply the following formula in Cell G6 and press Enter.
=IF($F$4="Shop 1",VLOOKUP(G5,B5:D9,2,FALSE),VLOOKUP(G5,B5:D9,3,FALSE))
6. Apply VLOOKUP for Multiple Conditions Range with IF Statement for Logical Test
Now, we will check if the result from the VLOOKUP function is equal to a specific text. Follow the given steps for that.
- Just use the following formula in Cell G5.
=IF(VLOOKUP(G4,$B$5:$D$9,2,FALSE)="Available","In Stock","Not in Stock")- Then, press Enter.
Read More: How to Prepare IF Statement Contains Multiple Words in Excel
7. Insert VLOOKUP and IF Statement for Multiple Dates with AND Condition in Excel
Additionally, we can apply VLOOKUP for dates. So, learn the steps of this example to apply VLOOKUP for multiple dates with IF statement using AND condition in Excel.
- Firstly, write the following formula in Cell G4.
=VLOOKUP(F4,IF((C5:C9>=F5)*(C5:C9<=F6),B5:D9,""),3,FALSE)- Then, press Enter.
Read More: How to Use IF Function with OR and AND Statement in Excel
An Alternative to VLOOKUP with IF Statement in Excel
We can also use a combination of the INDEX and MATCH functions instead of VLOOKUP with IF function. Let’s follow the steps given below for this example.
- Simply, write the following formula in Cell F5.
=IF(ISNA(INDEX(C5:C9,MATCH(F4,$B$5:$B$9,0))),"Not found",INDEX(C5:C9,MATCH(F4,$B$5:$B$9,0)))- Then, press Enter.
Conclusion
In this article, we have discussed the examples of Excel IF Statement with VLOOKUP for Multiple Conditions Range. Hope you will be able to use these functions easily. Please leave comments if you have any suggestions. Visit our ExcelDemy Website for more articles regarding Excel.
