Excel IF Statement with VLOOKUP for Multiple Conditions Range

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Use of IF Statement with VLOOKUP to Get Yes/No Result

In the formula, the VLOOKUP function searches for the value of Cell F4 in range $B$5:$C$9 and gives the value of column 2 of the range. The IF function checks whether the value from the VLOOKUP function is 0 or not and gives the respective Yes/No result.

Read More: How to Use IF Statement with Yes or No in Excel 


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)

Use of MAX function to Compare VLOOKUP Result with Another Cell Using IF Statement

  • 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")

Comparing VLOOKUP Result with Another Cell Using IF Satement for multiple conditions range

In the formula, the VLOOKUP function searches for the value of Cell F5 in range $B$5:$C$9 and gives the value of column 2 of the range. The IF function checks whether the value from the VLOOKUP function is equal to the maximum value (Cell F4) or not and gives the respective Yes/No result.

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")

Ignoring Error from VLOOKUP and IF Statement Applying ISNA Function in Excel

In the formula, if the VLOOKUP function doesn’t find the value it will give an error. The ISNA function alternates the error with a True value and vice-versa.

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))

Performing Different Calculations on VLOOKUP and IF Statement Result for multiple conditions range

In the formula, the VLOOKUP function searches for the value of Cell F4 in range $B$5:$C$9 and gives the value of column 2 of the range. The IF function checks whether the value from the VLOOKUP function is greater than or equal to 100 or not and multiplies the value from the VLOOKUP with 70% as True value and shows the original value as False value.

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))

Getting Data from Table Based on Another Cell Using VLOOKUP and IF Statement for multiple conditions range

In the formula, the IF function checks whether the value in Cell F4 is Shop 1 or not and gives true value VLOOKUP(G5,B5:D9,2,FALSE) where the VLOOKUP searches value of Cell G5 in range B5:D9 and gives value from column 2 of that range. For the False value of the IF function the VLOOKUP gives value from column 3.

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.

Applying VLOOKUP for Multiple Conditions Range with IF Statement for Logical Test

In the formula, the VLOOKUP function searches the value of Cell G4 in the range $B$5:$D$9 and gives the value from column 2 of that range. The IF function checks if the output from VLOOKUP is equal to text “Available” and gives True value “In Stock” and False value “Not in Stock”.

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.

Inserting VLOOKUP and IF Statement for Multiple Dates with AND Condition

In the formula, the IF function compares each cell of the range C5:C9 with F5 and F6 cell values. Subsequently, it returns the range B5:D8 as the logical test is true. Finally, the VLOOKUP function seeks the F4 cell value (Grape) in the range B5:D9 and returns the value in the 3rd column (George).

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.

Formula for An Alternative to VLOOKUP with IF Statement in Excel

In the formula, the MATCH function searches for the value of Cell F4 in range $B$5:$B$9 and gives the row number. The INDEX function takes the row number as an argument and gives the value from range C5:C9. The ISNA function checks for error in (INDEX(C5:C9, MATCH(F4, $B$5:$B$9, 0)). If error remains, the function returns True and vice-versa. This True/False value acts as a condition for the IF function and gives output “Not found” for True and INDEX(C5:C9, MATCH(F4, $B$5:$B$9, 0)) for False.

Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo