VLOOKUP with IF Condition in Excel (6 Examples)

Get FREE Advanced Excel Exercises with Solutions!

VLOOKUP with IF Condition in Excel means working with cases or Excel formulas where both VLOOKUP and IF functions are used.

In this Excel tutorial, we’ll learn how to use VLOOKUP with the IF condition in Excel.

For example, in the following GIF, we display the unit price of the selected products in 2 different stores: Walmart and Kroger. We applied the VLOOKUP function with the IF condition to extract the unit prices. The Data Validation drop-down list is being used to select the store and product.

vlookup with if condition

We can use VLOOKUP with the IF condition to look up a specific value; compare two lists for matches; look based on two values; compare VLOOKUP output with another cell; and perform multiple calculations. We will also see how to handle #N/A error while using VLOOKUP with IF condition.


What Is Excel IF Function?

The IF function in Excel tests a condition. If the condition is met, it returns one specified value, otherwise, it returns another specified value.

The following overview image shows the use of the function to determine the status of different products based on the sales target and sales achieved.

overview of if function


What Is Excel VLOOKUP Function?

The VLOOKUP function looks for a specified value in the leftmost column of a given table. It then returns the value in the same row from the specified column.

The following overview image shows the use of the function to extract the sales of Grape from the table.

overview of vlookup


1. Looking Up a Specific Value Combining VLOOKUP with IF Condition

In this section, we’ll look up a specific value in a given range by combining VLOOKUP with the IF condition in Excel.

Let’s get introduced to the dataset first. We have some products and their respective quantities. Here, we’ll check whether a selected product is in stock or not based on the quantity and thus, display the status.

dataset of products and quantity

So follow the steps below to perform the task.

Steps:

  • Select cell F7 => Write the below formula:
=IF(VLOOKUP(F6,B7:C15,2,FALSE)=0,"Out of Stock","In Stock")
  • Press Enter.

vlookup with if condition to check status of products

Formula Breakdown

=IF(VLOOKUP(F6,B7:C15,2,FALSE)=0,”Out of Stock”,”In Stock”)

=IF(125=0,”Out of Stock”,”In Stock”) // VLOOKUP(F6,B7:C15,2,FALSE) returns 125 because F6 (Pencil) is found in the 2nd row of the range B7:C15 and the intersection of 2nd row and 2nd column is 125.

=In Stock // IF(125=0,”Out of Stock”,”In Stock”) returns “In Stock” as 125 is not equal to 0 in the logical test.

The following GIF displays the status of a few products randomly. In this example, we select the product using the Data Validation drop-down list. In this way, we don’t have to manually type the product name.

gif of checking status of products


2. Comparing Two Lists for Matches Using VLOOKUP, IF, and ISNA Functions in Excel

In this part, we’ll compare two lists for matches using VLOOKUP, IF, and ISNA functions in Excel.

Let’s see the dataset first. The following image presents 2 lists where List 1 has some products and List 2 has only the sold-out products. Here, we’ll check the availability of the List 1 products by comparing them to List 2.

2 lists to compare

Learn the steps to complete the task.

Steps:

  • Select cell C7 => Type the below formula:
=IF(ISNA(VLOOKUP(B7,$E$7:$E$9,1,FALSE)),"In Stock","Sold")
  • Press Enter => Drag the Fill Handle.

Thus you will see the availability of the List 1 products.

vlookup with if condition to compare 2 lists

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


3. Using VLOOKUP and IF Condition to Lookup Based on Two Values

In this section, we’ll use the VLOOKUP with IF condition to look up based on two values.

In the below dataset, we have some products and their unit prices in 2 different stores: Walmart and Kroger. Here we’ll extract the unit price of a selected product from the specified store.

dataset of products and unit prices in 2 stores

So follow the steps below.

Steps:

  • Select cell G9 => Write the below formula:
=IF(G7="Walmart",VLOOKUP(G8,B8:D16,2,FALSE),VLOOKUP(G8,B8:D16,3,FALSE))
  • Press Enter.

vlookup with if condition to lookup based on 2 values

The following GIF shows the unit price of the products. Here, we select the product and the store using the Data Validation drop-down list.

gif of looking up based on 2 values

We can also use the IF condition in the VLOOKUP argument and they will give the same output as before. The Column Index Number of the VLOOKUP function is made dynamic by using the IF condition here. The formula is:

=VLOOKUP(G8,B8:D16,IF(G7="Walmart", 2, 3),FALSE)

vlookup with if condition for dynamic col index num


4. Comparing VLOOKUP Output with Another Cell Value in Excel

In this part, we’ll compare the VLOOKUP output with another cell value using the IF condition in Excel.

Let’s understand the dataset first. The following data presents some products and their corresponding sales. Here we find out the maximum sales by using the MAX function formula:

=MAX(C7:C15)

Now, we’ll find out which product has generated the maximum sales by comparing the sales figures with the above formula output.

dataset of products and sales

Learn the following steps to perform the task.

Steps:

  • Select cell C21 => Insert the formula:
=IF(VLOOKUP(C20,B7:C15,2,FALSE)>=C18,"Yes","No")
  • Press Enter. It’ll display the correct answer.

vlookup with if to compare vlookup output with another cell

The following GIF compares the sales figures of the selected products with the maximum sales value. In this example, we select the product using the Data Validation drop-down list. Hence, we don’t have to manually type the product name.

gif of comparing vlookup output


5. Performing Multiple Calculations by Using VLOOKUP with IF Condition

In this section, we’ll perform multiple calculations by using VLOOKUP with the IF condition.

Let’s see the dataset first. Here we present some salesman, with their respective product and sales. We’ll determine the total commission of a salesman based on their sales. For those who have sales greater than or equal to $200, the commission rate is 20%. Otherwise, they will receive a 10% commission.

dataset of salesman products and sales

Follow the steps below to find out the commission.

Steps:

  • Select cell G11 => Write the formula:
=IF(VLOOKUP(G10,B7:D15,3,FALSE )>=200, VLOOKUP(G10,B7:D15,3,FALSE)*G7, VLOOKUP(G10,B7:D15,3,FALSE)*G8)
  • Press Enter.

vlookup with if condition for multiple calculation

The following GIF finds out the total commission of the selected salesman. We select the salesman using the Data Validation drop-down list.

gif of peforming multiple calculations


6. Handling Error of VLOOKUP with IF Condition in Excel

In this last section, we’ll show how to handle the #N/A error spilled by VLOOKUP using the IF condition with ISNA. VLOOKUP spills this error when a lookup value is not found in the given table/cell range.

In the following image, as the Laptop is not found in the product list of the dataset, the formula spills the #N/A error. In this example, we’ll present three scenarios to deal with this error.

error output of vlookup

To display a particular text e.g. Not Found, when the lookup value is not found, use the below formula:

=IF(ISNA(VLOOKUP(C17,B7:C15,2,FALSE)), "Not Found", VLOOKUP(C17,B7:C15,2,FALSE))

For displaying 0 when the lookup value is not found, use the formula:

=IF(ISNA(VLOOKUP(C17,B7:C15,2,FALSE)), 0, VLOOKUP(C17,B7:C15,2,FALSE))

To display a blank cell when the lookup value is not found, use the formula:

=IF(ISNA(VLOOKUP(C17,B7:C15,2,FALSE)), "", VLOOKUP(C17,B7:C15,2,FALSE))

vlookup with if condition to handle errors

NOTE: ISNA is available in all Excel versions. But we have to use ISNA with IF to deal with the error. On the other hand, IFNA can handle #N/A errors without using IF. IFNA is available in Excel 2013 and later versions.

To handle all sorts of errors (not only #N/A) combine VLOOKUP with the IFERROR function.


Download Practice Workbook


This article has shown the use of VLOOKUP with IF condition through some practical examples. Moreover, it has shown how to deal with VLOOKUP spilled #N/A errors. Leave a comment for any further queries.


VLOOKUP with IF: Knowledge Hub


<< Go Back to Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

13 Comments
  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:
    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

  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
    ‘=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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo