# 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. 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.

### 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")` 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.

### 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")` 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))` 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.

### 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))` 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. 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”.

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

## 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. 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.

## 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.

## Related Articles #### Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  