IF and VLOOKUP Nested Function in Excel (5 Suitable Examples)

IF and VLOOKUP functions are some of the most used functions in MS Excel for various purposes. The IF statement is used for conditioning purposes and VLOOKUP is used for searching for any specific value in a range. The formulas using these two functions can easily search for any specific values from any given dataset. In this article, I will show you 5 practical examples of the IF and VLOOKUP nested functions in Excel.

IF and VLOOKUP Nested Function in Excel: 5 Practical Uses

1. Matching VLOOKUP Output with a Specific Value

Let’s consider a dataset of products with their ID, Name, Unit Price, Quantity, Total Amount, and Delivery Date.

Now, we want to find out the availability of each product using the IF and VLOOKUP formula. We want to search for the availability of the product by just entering the name of the product. Follow the steps below to achieve this.

📌 Steps:

• First and foremost, click on cell C17.
• Following, enter the following formula in cell C17 and press Enter.

•  Now, enter any name of the product that has a quantity greater than zero on cell C16 and check the output.

• Now, insert any name of the products for which the quantity is 0 in cell C16. And, you would see the result would come as No.

Thus, you have created a formula using the IF and VLOOKUP functions to return results for a specific value.

Read More: How to Use Nested VLOOKUP in Excel 

2. Using IF and VLOOKUP Nested Formula to Lookup Based on Two Values

Now we will search for any elements or products based on two values using the IF and VLOOKUP nested function. In the dataset, there are two different market prices for each product. Here are two values: a product ID and a market number.

Now, we want to find the product’s price based on these two values. Go through the steps below to do this.

📌 Steps:

• First, enter the following formula in cell C18 and press the Enter key.

• Following, enter the ID in cell C16 and Market No in cell C17.
•  Subsequently, press the Enter key.

Thus, you will be able to create a formula with IF and VLOOKUP nested functions to look up based on two values successfully.

Read More: Excel LOOKUP vs VLOOKUP

3. Matching Lookup Returns with Another Cell

Now, in this part, we will find out the highest price from the data and compare if our searched data matches the entered data or not. The highest salary can be pre-defined by using the MAX function.

Now, to achieve the actual target, follow the steps below.

📌 Steps:

• At the very beginning,  enter the following formula in cell C17 and press Enter.

• Following, enter any ID on cell C16 and check the output on cell C17.

Thus, you will be able to look up a value through another cell.

Read More: Return the Highest Value Using VLOOKUP Function in Excel

4. Using Nested Function with IF & VLOOKUP to Lookup Values from a Shorter List

At this time, we want to sort or extract some specific data from a list using IF and VLOOKUP nested functions. Let’s assume an additional column is added to the previous dataset named Status. There are two possible values for this attribute, one is Delivered and Not Delivered. Our task is to define the status of each product using the given Delivered Product List table information.

In order to accomplish this goal, go through the steps below.

📌 Steps:

• First and foremost, click on cell G5 and insert the following formula.

• Subsequently, hit the Enter key.

• Afterward, use the fill handle feature downward to copy the formula for all the other cells below.

Thus, you will see that you can look up values from a shortlist through these ways.

5. Use of IF-VLOOKUP Nested Function to Perform Different Calculations

Now in this section, we will perform more calculations based on the price of the products automatically.

Let’s say,  we want to find out the discount of 20% if the unit price is greater than $800 and the discount of 15% if the unit price is lower than $800. Follow the steps below to accomplish this.

📌 Steps:

• Initially, click on cell C17 and enter the following formula.

• Subsequently, press the Enter key.

• At this time, enter any ID on cell C16 and you will get the output in cell C17.

Consequently, you will be able to perform different calculations by using IF and VLOOKUP nested functions.

How to Handle Errors When Working with Nested Formula of IF and VLOOKUP Functions in Excel

Now, sometimes, it might happen that, there is no match according to your lookup. In this situation, you would get #N/A errors. But you can avoid showing this error using some tricks and formulas. Follow the ways below to learn this through the same dataset used in past examples.

1. IF ISNA VLOOKUP to Hide #N/A Errors

You can use the ISNA function with IF and VLOOKUP functions to avoid getting #N/A errors. Follow the steps below to do this.

📌 Steps:

• First and foremost, click on cell C17 and insert the following formula.

• Following, press the Enter key.

• At this time, enter any ID that is not in the dataset in cell C16.

• Consequently, you will get your desired result as Not found in cell C17 instead of the #N/A error.

As a result, you will be able to handle the #N/A errors in this way.

2. Return 0 for Missing Data Using IF and VLOOKUP Function

Now, let’s say, you want to return 0 instead of returning “Not found” when no data is matched. You can accomplish this by using the steps below.

📌 Steps:

• First, enter the following formula in cell C17 and press the Enter key.

• Afterward, enter any ID that is not in the dataset in cell C16.

Thus, you will get your desired result in cell C17 and will be able to handle #N/A errors with 0.

Things to Remember

In practice, you can get #N/A errors for the following reasons.

• The lookup value does not exist in the table
• The lookup value is misspelled or contains extra space.
• The table range is not entered correctly.
• You are copying VLOOKUP, and the table reference is not locked.

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, I have shown you 5 practical examples of the IF–VLOOKUP nested function in Excel. You can also download our free workbook to practice. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to comment here.

Further Readings

• Combining SUMPRODUCT and VLOOKUP Functions in Excel
• How to Use VLOOKUP with COUNTIF
• How to Combine SUMIF and VLOOKUP in Excel 
• Use VLOOKUP to Sum Multiple Rows in Excel
• INDEX MATCH vs VLOOKUP Function
• XLOOKUP vs VLOOKUP in Excel
• How to VLOOKUP and SUM Across Multiple Sheets in Excel
• How to Use VLOOKUP Function with INDIRECT Function in Excel
• How to Use IFERROR with VLOOKUP in Excel

<< Go Back to VLOOKUP with IF Condition | Excel VLOOKUP Function | Excel Functions | Learn Excel