Excel allows a user to combine the **IF** statement and the **VLOOKUP** functions. This step by step tutorial will assist all levels of Excel users to learn how to combine an IF statement and a lookup function in Excel.

*Figure 1. The result of the formula*

**Syntax of the VLOOKUP formula**

The generic formula for the VLOOKUP function is:

**=****VLOOKUP****(lookup_value, table_array, col_index_num, range_lookup)**

The parameters of the VLOOKUP function are:

**lookup_value**– a value that we want to find in a table_array**table_array**– a range in which we want to lookup**col_index_num**– a column number in table_array from which we would like to get a value**range_lookup**– default value is FALSE. This means that we want to find an exact match for a lookup value.

**Syntax of the IF Formula**

The generic formula for the IF function is:

**=IF(logical_test, value_if_true, value_if_false)**

The parameters of the IF function are:

**logical_test**– a logical expression that we want to check**value_if_true**– a value which the function returns if a logical_test is TRUE**value_if_false**– a value which the function returns if a logical_test is FALSE.

**Setting up Our Data for the Formula**

Let’s look at the structure of the data. In the range B2:C7 we have a table from which we want to pull data. The table has two columns “Product Description” (column B) and “Quantity” (column C). In the cell F2, we have the product which we want to check. In F3, we want to to get “Yes” if the quantity of the product is greater than 0, or “No” if it’s 0.

* Figure 2. The data structure for the formula*

**Check if a Product is Out of the Stock Using the Formula**

In this example, we want to check the quantity of “Product A”. If the quantity is 0, we will return “No” in F3 and if it’s greater than 0, we will return “Yes”.

The formula looks like:

**=IF(VLOOKUP(F2, B3:C7, 2, FALSE), "Yes", "No")**

In our example, the **lookup_value** is the F2 cell (“Product A”). The parameter **table_array** is B3:C7 because we want to find value from the range B3:B7. **Col_index_num** has value 2, as we want to pull value from the second column of the range. Finally, **range_lookup** has value FALSE, because we want to find an exact match of “Product ID” values. The result of the VLOOKUP function is the** logical_test** of the IF function. The **value_if_true** is “Yes” and the **value_if_false** is “No”.

To apply the VLOOKUP function, we need to follow these steps:

- Select cell F3 and click on it
- Insert the formula:
`=IF(VLOOKUP(F2, B3:C7, 2, FALSE), "Yes", "No")`

- Press enter

*Figure 3. Using the formula to check if the product is out of the stock*

The quantity for Product A in the cell C3 is 0. Therefore, the VLOOKUP function returns 0. This is considered as false logical condition, so the result in the cell F3 is “No”.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment