**VLOOKUP** is one of the most powerful and top-used functions in Excel. Using **IF logical function** with **VLOOKUP** makes the formulas more powerful. In this article, we shall see a good number of examples where we have paired Excel **VLOOKUP** **Function** with** IF condition**.

**Table of Contents**hide

## Excel IF Function

Check whether a condition is met, and return one value if **TRUE**, and another value if **FALSE**.

The **Syntax** of **IF** function:

`IF (logical_test, value_if_true, [value_if_false])`

**logical_test (required)**

The condition you want to test

**value_if_true (required)**

If the **logical_test** is **TRUE**, the **IF** function will return this value.

**value_if_false (optional)**

If the **logical_test** is **FALSE, **the** IF** function will return this value.

## Excel VLOOKUP Function

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be ordered in **ascending order**.

The **Syntax **of **VLOOKUP** function:

`VLOOKUP (lookup_value, table_array, column_index_num, [range_lookup])`

**lookup_value (required)**

This indicates the value you want to lookup using this function. Make sure the lookup_value is in the 1st column of your table_array.

**table_array (required)**

It is the Cell range from where you want to lookup a value.

**col_index_num (required)**

It is the column number of your given given cell range starting with 1 from the leftmost column.

**range_lookup (optional)**

This is an optional logical value that indicates if you want to find an approximate match or an exact match using this function.

**TRUE** will look for the closest value after considering that the first column of the table is ordered either numerically or alphabetically.

If you don’t indicate a method, this will be used by default.

**FALSE** will look for the first column’s precise value.

## Download Practice Workbook

## 7 Ways to Use VLOOKUP Function with IF Condition in Excel

Here, you will find **7** different ways with real-life examples to use the **VLOOKUP** **function** with **IF condition** in Excel.

### 1. Using VLOOKUP Function with IF Condition to Return In Stock/ Not in Stock in Excel

This is a good example if you manage **inventory **using Excel. In the following worksheet (top left corner), you’re seeing I have a table. The table lists some **Products **and their status under the **Availability** column.

Now, we will use the **VLOOKUP function **with the **IF condition **to return **In Stock **or **Not in Stock** in the 2nd Table.

Here are the steps.

**Steps:**

- After that, in the
**2**table (under the^{nd}**Shopping Cart**), under the**Status**column, and in Cell**C13**we will input this formula.

`=IF(VLOOKUP(B13, product_status, 2, FALSE)="Available", "In Stock", "Not in Stock")`

This formula is also self-explanatory from the above image. Here is the explanation for new Excel users:

Now, let’s explain the **logical_test **argument of this formula. We are using this formula **VLOOKUP(B14, product_status, 2, FALSE)=”Available” **as the **IF **function’s **logical_test** argument. If this part of the formula returns a **TRUE** value then the cell will show the **“In Stock”** value, otherwise it will show a **“Not in Stock”** value.

- Then, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

We have also used another **IF** and **VLOOKUP** combo under the **Price $** column.

- Now, this is the formula I have used in cell
**E13**.

`=IF(C13="In Stock", D13*VLOOKUP(B13,product_status,3, FALSE), "Coming soon...")`

Here, if the value of cell **C13** is **“In Stock”**, then the cell will show the value of this part of the formula: **D13*VLOOKUP(B13,product_status,3, FALSE)**. This formula is just a product of the **D13** cell value and a simple **VLOOKUP** formula.

If the value of cell **C13** is not **“In Stock”**, then the cell will show this value **“Coming soon…”**.

- After that, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Next, insert the following formula in Cell
**E17**.

`=SUM(E13:E16)`

- Finally, press
**ENTER**.

### 2. Using VLOOKUP Function with IF Condition for 2 Tables of Values

In this example, you will see how to use two or more **table arrays** in the **Excel VLOOKUP formula**.

**Steps:**

- Firstly, insert the following formula in Cell
**H5**.

`=VLOOKUP(G5, IF(F5="New", new_customer, old_customer), 2, TRUE)`

**Formula Breakdown**

**Firstly,**Cell**G5**is the**lookup_value**in the**VLOOKUP Function**and it is an amount under the**Sales**column.- Now,
**IF(F5=”New”, new_customer, old_customer):**This formula will return one of the two tables:**new_customer**and**old_customer**.**new_customer**=**$B$5:$C$9**and**old_customer**=**$B$13:$C$17**. - After that, the rest is simple. The
**column index number**is**2**. So, the**VLOOKUP function**will return the value from the**2**column of the same row where it finds the^{nd}**lookup value**.

We have used the **TRUE** value as the **range_lookup** argument, so the **VLOOKUP function **will search for the **closest value equal to or less than the lookup value**.

- Then, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will get all the values of
**Comm%**using the**VLOOKUP function**with**IF condition**from**2 tables**.

### 3. Using Data Validation Feature with VLOOKUP Function and IF Condition

Now, we will show you how to use the **Data Validation Feature **with the **VLOOKUP function **and **IF condition **in Excel.

Here, we have a dataset containing the **Product **list and the **Price **of two stores **Meena **and **Lavender**. Now, we will show you how to VLOOKUP this data in the **2nd **table.

Follow the steps given below to do it yourself.

**Steps:**

- Firstly, select Cell
**C4**. - Then, go to the
**Data tab**>> click on**Data Tools**>> click on**Data Validation**>> select**Data Validation**.

- Now, the
**Data Validation**box will appear. - After that, select
**List**as**Allow**and insert Cell range**C6:D6**as**Source**. - Next, click on
**OK**.

- Again, select Cell
**C4**. - Then, click on the
**Drop-down**button. - Now, select any
**Store**of your choice. Here, we will select**Meena**.

- Next, name Cell range
**B7:D111**as**shop_price**going through the steps shown in**Method1**. - After that, select Cell
**G7**and insert the following formula.

`=IF($C$4="Meena",VLOOKUP(F7,shop_price,2,FALSE),VLOOKUP(F7,shop_price,3,FALSE))`

**Formula Breakdown**

- In the beginning, the
**IF**Function tests whether**$C$4**cell value is equal to the value**Meena**. - Then, if the above logical test is
**TRUE**, it returns this part of the formula**VLOOKUP(F7, shop_price,2, FALSE)**. It is a straightforward**VLOOKUP**formula. It searches for the value of cell**F7**in the**shop_price**table array and if it finds it then returns the value of the**2**column of the same row.^{nd} - Otherwise, if the logical test is
**FALSE**, then it returns this part of the formula**VLOOKUP(F7, shop_price,3**,**FALSE)**. A simple**VLOOKUP**formula.**VLOOKUP**finds the value of the**F7**cell in the**shop_price**table array and if it finds it then returns the value of the**3**column of the same row.^{rd}

- Then, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Now, you will get all the
**Price**values of the**Products**of the**Meena**store.

- Next, select Cell
**I7**and insert the following formula.

`=G7*H7`

Here, in the formula, we **multiplied **Cell **G7 **with the value of Cell **H7 **to the **Total **price of the **Product**.

- Then, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Now, we will get all the
**Total**prices of the individual**Products**.

- After that, select Cell
**I12**and insert the following formula.

`=SUM(I7:I11)`

Here, in **the SUM Function**, we added all the values of the Cell range **I7:I11**.

- Finally, press
**ENTER**.

### 4. Selecting Col Index Num Argument of VLOOKUP Function Dynamically with IF Function

In the fourth method, we will show you how you can select the **Col Index Num** argument of the **VLOOKUP function** dynamically with the **IF function** in Excel.

Here are the steps.

**Steps:**

- In the beginning, name Cell range
**B4:E11**as**sales_table**going through the steps shown in**Method1**. - Then, create a drop-down button in Cell
**C14**using the**Data Validation feature**where insert Cell range**D4:E4**as**Source**going through the steps shown in**Method3**. - After that, select any option using the drop-down button. Here, we will select
**Projected**.

- Next, select Cell
**C17**and insert the following formula.

`=VLOOKUP(B17, sales_table, 2, FALSE)`

Here, in the **VLOOKUP Function**, we inserted Cell **B7 **as **lookup_value**, **sales_table **named range as **table_array**, **2 **as **col_index_num, **and **FALSE **as **range_lookup**.

- Now, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Then, select Cell
**C24**and insert the following formula.

`=SUM(C17:C23)`

Here, in the **SUM Function**, we added the values of Cell range **C17:C23** to get the amount of **Total Target**.

- After that, press
**ENTER**.

- Next, select Cell
**D16**and insert the following formula.

`=VLOOKUP(B16, sales_table, IF($C$14="Projected", 3, 4), FALSE)`

This is a straightforward simple **VLOOKUP **formula. We have just made the **col_index_num** argument part **dynamic **using an **IF function**.

This part of the formula demands a little discussion: **IF($C$14=”Projected”, 3, 4)**. If the Cell **$C$14** value is equal to the value **Projected**, the IF function will return **3**, otherwise, it will return **4**. So, this is **dynamically **selecting the **column index number** of the **VLOOKUP formula**.

- Afterward, press
**ENTER**and drag down the**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Then, select Cell
**D24**and insert the following formula.

`=SUM(D17:D23)`

Here, in the **SUM** Function, we added the values of Cell range **D17:D23** to get the amount of **Total Projected**.

- Finally, press
**ENTER**.

### 5. Using ISNA and IFERROR Function with VLOOKUP Functions and IF Condition in Excel

These two techniques will help you to handle **#N/A errors**. VLOOKUP generates a **#N/A** error when it does not find the value that you’re looking for.

Now, look at the following image intensively. Here, Cell **F6** shows the **#N/A** error as we did not handle the error smartly.

Follow the Steps given below to solve this error using the **ISNA** and **IFERROR** functions in Excel.

**Steps:**

- Firstly, select Cell
**F7**and insert the following formula.

`=IFERROR(VLOOKUP(E7,price_list,2,FALSE),"Not found")`

Here, as the **value** of the **IFERROR function**, we have input the **VLOOKUP **formula. If this **VLOOKUP **formula returns an **error**, then the **“Not found”** value will be shown in the **F7** cell.

- Then, press
**ENTER**. - Now, you can see that the
**error**has been removed.

- After that, to
**remove**the**error**using the**ISNA**function select Cell**F8**and insert the following formula.

`=IF(ISNA(VLOOKUP(E8,price_list,2,FALSE)), "Not found", VLOOKUP(E8, price_list, 2, FALSE))`

**ISNA function **returns **TRUE **when it finds the **#N/A error**. I have shown it on the top right corner of the worksheet.

If this formula returns the **#N/A error**, then **ISNA **will return a **TRUE **value, and **IF **function’s **logical_test **argument will be **TRUE**. If this **VLOOKUP **formula returns a **real value**, ISNA will return a **FALSE **value.

So, if **ISNA **returns the **TRUE **value **IF **function this value **“Not found”** will be shown in cell **F8**. Otherwise, this formula will be executed: **VLOOKUP(E8, price_list, 2, FALSE)**. This is a straightforward **VLOOKUP** formula.

- Finally, press
**ENTER**to**remove**the**error**using the**ISNA function**.

### 6. Performing Multiple Calculations by Using VLOOKUP Function with IF Condition

Next, we will show you how to perform **multiple calculations **by using the **VLOOKUP Function **with the **IF condition**.

Here, we will select any **Salesman, **and depending on the **Sales **value we will calculate **Comm%** using the **VLOOKUP Function **with **IF condition**.

Follow the steps given below to it on your own.

**Steps:**

- Firstly, create a drop-down button in Cell
**G4**using the**Data Validation feature**where insert Cell range**B5:B9**as**Source**going through the steps shown in**Method3**. - Next, select any
**Salesman**from the drop-down list. Here, we will select Salesman**A**.

- Then, select Cell
**G5**and insert the following formula.

`=IF(VLOOKUP(G4,$B$5:$D$9,3,FALSE)>=150,VLOOKUP(G4,$B$5:$D$9,3,FALSE)*30%,VLOOKUP(G4,$B$5:$D$9,3,FALSE)*15%)`

**Formula Breakdown**

- Firstly, in the IF function, we set
**VLOOKUP(G4,$B$5:$D$9,3,FALSE)>=150**as**logical_test**. It will check if the value in Cell**G4**is greater than or equal to 150 by using the**VLOOKUP Function**in Cell range**B5:D9**and in the**3rd**column. - Then, if the function returns
**TRUE**, it will find the value of**Sales**from Cell range**B5:D9**and in the**3rd**column using the**VLOOKUP Function**and after that**multiply**it with**30%**. - Otherwise, it will
**multiply**the VLookup value by**15%**.

- Finally, press
**ENTER**to get the value of**Comm%**.

### 7. Comparing Vlookup Value with Another Cell Value

In the final method, we will show you how to **compare the Vlookup value **with **another cell **value using the **VLOOKUP Function **with the **IF condition**.

Firstly, we will calculate the **Max Sales **value and then check if the product in Cell **G5 **is **Max **or not.

Go through the steps given below to do it on your own.

**Steps:**

- In the beginning, select Cell
**F4**and insert the following formula.

`=MAX(D5:D9)`

Here, in **the MAX function**, we inserted Cell range **D5:D9** as a number to identify the **maximum **value.

- Then, press
**ENTER**.

- After that, create a drop-down button in Cell
**G5**using the**Data Validation feature**where you insert Cell range**C5:C9**as**Source**going through the steps shown in**Method3**. - Next, select any
**Product**from the drop-down list. Here, we will select**Egg**.

- Now, select Cell
**I5**and insert the following formula.

`=IF(VLOOKUP($G$5,$C$5:$D$9,2,FALSE)>=$G$4,"Yes","No")`

**Formula Breakdown**

- Firstly, in the IF function, we set
**VLOOKUP($G$5,$C$5:$D$9,2,FALSE)>=$G$4**as**logical_test**. It will check if the value in Cell**G5**is greater than or equal to the value in Cell**G4**by using the**VLOOKUP Function**in Cell range**C5:D9**and in the**2nd**column. - Then, if the function is
**TRUE**, it will return**“Yes”**. - Otherwise, if the function is
**FALSE**, it will return**“No”**.

- Finally, press
**ENTER**.

## Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

## Conclusion

So, in this article, you will find **7 **ways to use the** VLOOKUP Function **with **IF Condition **in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit **ExcelDemy** for many more articles like this. Thank you!

In the 4th way, I commonly use MATCH instead IF, because if I have more than two items, MATCH works very well

The col_index argument will be like this:

MATCH($C$14,$B$4:$E$4,0)

By the way, great job!! Keep going!

Have a nice day!

Hi Daniel,

Thanks for your feedback.

Yes. INDEX and MATCH combo work in a more versatile way than the VLOOKUP function. I did not use Index Match because I wanted to show all the examples with VLOOKUP and IF Functions.

Keep in touch.

Best regards

Kawser

interesting function & I can’t dispense it <3

Thanks for your feedback 🙂

Congratulations, great!

I want to user function which you shown in screen in VBA code , but it’s not run.

I m using 3 different sheet.

1 sheet lookup value pick d2 col and also check f2 value .

Range another sheet2 case 1 and case 2,value need

And results sheet3

Thanks, Ferreira!

Thank you for the useful tutorial, it explained the uses very easily and effectively. 🙂

Nice code, but your explanations are rather condescending. “For the complete layman”…? Comes off as superior.

Maybe a little bit tough for the complete layman. I shall change my Phrase then 🙂

Hi,

I’m struggling to find the right formula to multiply units by rates.

I have different materials and tasks with different units and rates are depend on quantities. Some of the units only have one rate with no conditions.

I have a more than 2000 row spreadsheet and units also varies that means that the formula also need to find the unit on sheet 1. Rate criteria can also change on sheet 1.

I’m looking for the price on Sheet2.

I believe the below formula need to be combined with vlookup but I cannot get it to work

‘=IF(C210,C250,C2200,C2*G2,0))))

Many thanks for your help!

Niki

Sheet 1

Unit”Rate1(not exceeding)””Rate2(not exceeding)””Rate3(not exceeding)””Rate4(exceeding)”

day

h

m (QB) 10 50 200 200

m

m2 (QB) 10 50 150 150

m2

Sheet 2

Description Unit Quantity Rate 1 Rate 2 Rate 3 Rate 4 Price

path m (QB) 10 11 13 13.5 14

road m (QB) 51 5 10 15 20

wall m2 (QB) 35 10 15 20 25

wood m 20 11

paint m2 150 12

Is there a way to add another criteria to this formula? I want to add if this range has a certain word, in my case OUTSTANDING, to return BLANK””. Current Formula: IFERROR(VLOOKUP(B9,’REPORT!A:C,3,FALSE),””)

Hi there!

I couldn’t fully understand what you need from your comment. However, I assumed you may want to create something like the following formula.

`=IFERROR(IF(REPORT!A:C="OUTSTANDING","",VLOOKUP(B9,REPORT!A:C,3,FALSE)),"")`

Is this what you needed? If not, then tell us more about the problem so that we may help you. Thank you for being with us.

Regards

Md. Shamim Reza (ExcelDemy Team)