Using VLOOKUP with IF Condition in Excel (5 Real-Life Examples)

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

IF Function Excel

Excel VLOOKUP Function

Excel VLOOKUP function

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

How does this formula work?

VLOOKUP with IF condition in Excel example

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 2nd 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 3rd 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.

If and Vlookup with 2 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 2nd 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.

Using VLOOKUP as logical test of an IF function

In the 2nd 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 😊

Further Readings



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

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

    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

  2. interesting function & I can’t dispense it <3

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

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

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

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

    Many thanks for your help!


    Sheet 1
    Unit”Rate1(not exceeding)””Rate2(not exceeding)””Rate3(not exceeding)””Rate4(exceeding)”
    m (QB) 10 50 200 200
    m2 (QB) 10 50 150 150

    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

Leave a reply