How to use IF and VLOOKUP nested functions in Excel – 5 examples

If you want to learn how to use IF and VLOOKUP nested functions in Excel, you’ve come to the right place.

The IF and VLOOKUP nested functions are two of the most powerful functions in Excel used by newbies and professionals alike. They can help you in many ways, such as performing calculations, looking for two specific values, performing yes and no functions, and much more.

In this guide, we’ll share five examples of how to use IF and VLOOKUP nested functions in Excel. So, without wasting another second, let’s dive in!

Let’s get started!

Using IF and VLOOKUP nested functions in Excel

Scenario on hand: We have a fictional dataset of product sales at a grocery store. 

What we want to accomplish: Explore 5 examples of the IF and VLOOKUP nested functions in action:

1. Using the IF and VLOOKUP nested functions for yes and no
2. Using the IF and VLOOKUP nested functions to perform calculations
3. Using the IF and VLOOKUP nested formula to look for two values
4. Using the IF and VLOOKUP nested formula to match VLOOKUP results with another cell
5. Using the IF and VLOOKUP nested functions to look up values from a shorter list

Example 1: Using the IF and VLOOKUP nested functions for yes and no

Our dataset includes the produce code, product name, category, and inventory. We want to search whether a product is available or not using its code. 

We create a search box with the code name like this:

Next, we enter the following formula:

=IF(VLOOKUP({Cell address of lookup value},{Table array}, {Column index number},FALSE)=0,”{Value if true}”,”{Value if not true}”)

=IF(VLOOKUP(H5,B6:E14,4,FALSE)=0,”No”,”Yes”)

The formula returns “No” since the product inventory is 0. 

Let’s test it with a product code that is in stock. 

We add the code VY-5831 for the product Serene Green Jasmine Tea. 

Since we changed the code name in the cell address of the lookup value, we don’t need to change the formula. 

Here’s what we get:

Now, let’s test the formula with the product name:

Since we are using the product name instead of the code, we need to change the formula in two ways:

1. Change the table array so the product name column is the first column of the array. 
2. Change the column index number according to the change in the table array. 

Here’s the new formula we used:

=IF(VLOOKUP(H5,C6:E14,3,FALSE)=0,”No”,”Yes”)

Note that we changed the table array from B6:E14 to C6:E14 and the column index number from 4 to 3. 

Here’s the result we got:

Example 2: Using the IF and VLOOKUP nested functions to perform calculations

We added our price column to our data. We need to add another column for member prices. 

Members are offered a 20% discount on electronics, a 15% discount on beauty products, and a 5% discount on food and beverages. 

If the customer is a member, the VLOOKUP formula calculates the discounted price based on the table. If not, it returns the original price. 

Here’s the formula we use for this calculation:

=IF({Cell address of lookup value with $}=”{Certain value}”,{Cell address with original number} *(VLOOKUP({Cell address of lookup value},{Table array with $}, {Column index number},FALSE)), {Cell address with original number})

=IF($I$3=”Yes”,E6*(VLOOKUP(D6,$H$6:$I$8,2,FALSE)), E6)

Here’s what that yields for the first product:

We pull down the formula to the rest of the table using the Fill handle tool. The parts of the formula without the $ sign dynamically change to return the right price for each row:

When we change the member to “No”, here are the results:

Example 3: Using the IF and VLOOKUP nested functions to look for two values

For this example, we have removed the inventory column and added two price columns. The first column is the regular price, and the second column is the member price. 

We then create a search box where we input the product name and a Yes/ No cell for members. 

Here is how our worksheet looks:

We now add a product name and type yes for the member. 

We then add this formula:

=IF({Cell address of lookup value 1}={Specific value},VLOOKUP({Cell address of lookup value 2},{Table array}, {Column index number},FALSE),VLOOKUP({Cell address of lookup value 2},{Table array}, {Column index number},FALSE)

For our data, here’s how we use this formula:

=IF(I6=”No”,VLOOKUP(I5,C6:F14,3,FALSE),VLOOKUP(I5,C6:F14,4,FALSE))

According to this formula, we instruct Excel to:

• Look for the value in cell I6. 
• If the value is “No,” it has to look for the product name in I5 and return the value from the 3rd column of the table array. 
• If the value in I6 is not “No,” it will look for the product name in I5 and return the value from the 4th column of the table array. 

Here’s the formula in action:

We change the data type to “Currency” from the top to get a $ sign on the result. 

Example 4: Using the IF and VLOOKUP nested functions to match

The supermarket offers customers a 15% flat discount if their total exceeds $250. However, this discount is only applied to regular prices. 

We have a basket of three goods that a customer purchased. We need to use VLOOKUP and IF to determine whether the customer is eligible for the discount.

Here’s the data:

Then, we enter the following formula:

=IF(VLOOKUP({Cell address of lookup value},{Table array},{Column index number})+(VLOOKUP({Cell address of lookup value},{Table array},{Column index number}) +(VLOOKUP({Cell address of lookup value},{Table array},{Column index number}) >={cut off value},”{Value if true}”,”{Value if false}”)

=IF((VLOOKUP(H6,C6:F14,3, FALSE)+VLOOKUP(H7,C6:F14,3, FALSE)+VLOOKUP(H8,C6:F14,3, FALSE))>=I12, “Yes”, “No”)

According to the formula, the customer is not eligible for the flat 15% discount:

Example 5: Using the IF and VLOOKUP nested functions to look up values from a shorter list

The superstore has announced a 20% discount on electronics and beauty categories. 

Here’s the formula we use to determine whether a certain product is discounted or not:

=IF(ISNA(VLOOKUP({Cell address of lookup value}, {Table array with $},{Column index number},FALSE)),”{Value if not found}”, “{Value if found}”)

Here’s the formula for our dataset:

=IF(ISNA(VLOOKUP(D6,$H$6:$H$7,1,FALSE)),”Not Discounted”, “Discounted”)

Let’s look at the formula in action:

Expanding this formula to the rest of the table:

Alternatively, we can also add calculations to the formula in place of “Value if found” and “Value if not found” like this:

=IF(ISNA(VLOOKUP(D6,$H$6:$H$7,1,FALSE)),E6,E6*80%)

Here, we have entered the calculations in the formula. 

• If the category is not found in the shorter list, it will return the same price. 
• If the category is found in the shorter list, it will return the price multiplied by 80% (meaning 20% discount). 

Let’s see the formula in action:

Wrapping up

These five examples showcase how you can use the IF and VLOOKUP nested functions in Excel. There are various other ways that you can use the formula as well. If you want to learn more about Excel and how its functions work, check out these in-depth helpful guides:

• How to add and use Developer tab in Excel
• How to autofill dates in Excel
• How to convert text to numbers in Excel