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 VLOOKUP with IF condition in Excel.

At first, letโs take a closer look at both the Excel IF and VLOOKUP functions. This will make it easy to understand the formulas.

## Excel IF Function

## Excel VLOOKUP Function

## 1) Using VLOOKUP and IF condition to Choose the best Bargain Store

### How does this formula work?

This is a very simple formula. Let me explain the formula in cell `G8 = IF($C$4="Meena",VLOOKUP(F8,shop_price,2,FALSE),VLOOKUP(F8,shop_price,3,FALSE))`

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

## 2) Excel VLOOKUP with 2 Tables of Values

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

In the following image, youโre seeing how I have made a formula using **VLOOKUP and IF functions** to choose one of the two table arrays.

### How does this formula work?

The image above is self-explanatory. Here is the explanation for the complete laymen:

- In cell
**H5**, I have used this formula:`=VLOOKUP(G5, IF(F5="New", new_customer, old_customer), 2, TRUE)`

**G5**is the**lookup_value**here and it is an amount under theย**Sales**ย column.`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**.- The rest is simple. The column index number is 2. So, the VLOOKUP function will return the value from the 2
^{nd}column of the same row where it finds the lookup value. - I have used
**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**.

## 3) Using VLOOKUP function as the logical test of IF function (good for inventory management)

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.

In the 2^{nd} table (under the **Shopping Cart**), under the **Status** column, and in cell C14 I have input this formula:

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

### How does this formula work?

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

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 **โIn Stockโ** value, otherwise it will show **โNot in Stockโ** value.

Check out the image below.

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

This is the formula I have used in cell **E14** `=IF(C14="In Stock", D14*VLOOKUP(B14,product_status,3, FALSE), "Coming soon...")`

If the value of cell **C14** is **โIn Stockโ**, then the cell will show the value of this part of the formula: **D14*VLOOKUP(B14,product_status,3, FALSE)**. This formula is just a product of **D14** cell value and a simple VLOOKUP formula.

If the value of cell **C14** is not **โIn Stockโ**, then the cell will show this value **โComing soonโฆโ**.

## 4) Selecting col index num argument of VLOOKUP function dynamically with IF function

Look at the following image. You see when I select **Projected**, it is selecting the values under the **Projected** column of the first table (on the top left corner of the worksheet). When I am selecting the **Actual Sales** value, the table below is showing the values of the **Actual Sales** column from the first table.

This technique will help you a lot when you will make dashboards using Excel.

In cell **C14**, I have made a data validation to show only two values: **Projected** and **Actual Sales**.

Then in cell **D16**, I have used this formulaย `=VLOOKUP(B16, sales_table, IF($C$14="Projected", 3, 4), FALSE)`

. Then dragged down this formula for other cells in the same column.

### How does this formula work?

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 **$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. Quite interesting!

## 5) VLOOKUP with IFERROR and IF + ISNA Functions

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

**Read more:** How to Use IF ISNA Function with VLOOKUP in Excel

Look at the following image intensively.

Cell **F6** shows the **#N/A** error as we did not handle the error smartly.

But cell **F7** and **F8** do not show any error though VLOOKUP did not find the **Noodles** product in the leftmost table (our table_array).

Letโs explain these two formulas:

### Cell F7 Formula

In cell **F7**, we have this formula: `=IFERROR(VLOOKUP(E7,price_list,2,FALSE),"Not found")`

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

### Cell F8 Formula

Letโs explain the formula of cell **F8**. We have used this formula in cell F8: `=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.

As the ISNA functionโs **value**, I have input the VLOOKUP formula: `VLOOKUP(E8,price_list,2`

, FALSE`)`

.

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 **FALSE** value.

So, if **ISNA** returns the **TRUE** value, **IF** functionโs 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.

## Download Working File

So, these are my ways to use VLOOKUP with IF condition in Excel. I have shown total of 5 examples.

Do you know any other ways? If you want to add your technique to this article (with credit), please let me know in the comment box. I will update this article.

Happy Excelling ๐

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