# 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 function in Excel.

## 5 Practical Uses of IF and VLOOKUP Nested Function in Excel

### 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.
`=IF(VLOOKUP(C16,\$C\$5:\$D\$14,2,FALSE)=0,"No","Yes")` 🔎 Formula Breakdown:

• Firstly, in the VLOOKUP function C16 is the cell that is holding the search keyword. Then \$C\$5:\$D\$14 is the range where we will search the entered data. 2 is used as we are looking for matched criteria value in the second column of our lookup range and lastly FALSE is used to define the exact match.
• So, VLOOKUP(C16,\$C\$5:\$D\$14,2, FALSE) will return the value of Quantity column for cell C16.
• Then the IF function checks whether the output of the VLOOKUP function is 0 or something else. Depending on the result, the IF function will return Yes or No as the final output.
•  Now, enter any name of the product which 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.

### 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.
`=IF(C17="Market 1",VLOOKUP(C16,B5:E14,3,FALSE),VLOOKUP(C16,B5:E14,4,FALSE))` 🔎 Formula Breakdown:

• In the IF function C17=”Market 1″ is the logical condition. It is checking whether the entered Market No is 1 or not.
• If the Market No is 1 then the price will be extracted from the Market 1 column using the VLOOKUP(C16,B5:E14,3,FALSE) part.
• Otherwise, it will extract the price from the Market 2 column using VLOOKUP(C16,B5:E14,4,FALSE) this subformula.
• 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: With 3 Examples

### 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.
`=IF(VLOOKUP(C16,\$B\$5:\$G\$14,4)>=F16,"Yes","No")` 🔎 Formula Breakdown:

• In the IF function, firstly we are checking the condition using the VLOOKUP function’s return value. VLOOKUP(C16,\$B\$5:\$G\$14,4) this part will return the price of the entered ID and will be compared with the predefined maximum value.
• If the entered ID’s price is greater than or equal to the highest price, then it will print Yes, otherwise it will print No.
• 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.

### 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.
`=IF(ISNA(VLOOKUP(C5,\$I\$5:\$I\$10,1,FALSE)),"Not Delivered","Delivered")` 🔎 Formula Breakdown:

• Additionally, we have used a combination of IF, ISNA, and VLOOKUP functions here. If the ISNA function could not find the matched data name from the lookup range, then it will return TRUE, otherwise FALSE.
• Using the return value of the ISNA function, the IF function returns “Not Delivered” if the product is not found in the lookup range and “Delivered” if the product is in the lookup range.
• 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 will be able to 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.
`=IF(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE )>800, VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)*15%, VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)*20%)`
• Subsequently, press the Enter key. 🔎 Formula Breakdown:

• VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE )>800, this part will check if the C16 cell’s lookup value in the Unit Price column is greater than 800.
• =IF(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE )>800,VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)*15%,VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)*20%), this part ensures that if the looked up value is greater than 800, it would be multiplied with 15%, else, it would be multiplied with 20%.
• 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.
`=IF(ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)),"Not found",VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE))`
• Following, press the Enter key. 🔎 Formula Breakdown:

• VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE), this part finds the unit price from the Unit Price column for the product ID that is in cell C16.
• ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)), this part checks if the desired unit price value is available in the dataset or not.
• =IF(ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)),”Not found”,VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)), this formula returns “Not found” if the value does not exist in the dataset, and returns “Found” if the value exists in the dataset.
• At this time, enter any ID that is not in the dataset in cell C16. 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.
`=IF(ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)),0,VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE))` 🔎 Formula Breakdown:

• ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)), this part checks if the C16 cell’s lookup unit price value is available in the dataset or not.
• =IF(ISNA(VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)),0,VLOOKUP(C16,\$B\$5:\$F\$14,4,FALSE)), this part returns 0 if the value is not found in the dataset and returns the actual unit price of the desired product if found.
• 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. Note:

As the Price, the field is formatted as a currency that’s why it will not print 0 directly. Instead of 0, it will print a dash line (-).

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

## Conclusion

So, in this article, I have shown you 5 practical examples of the IFVLOOKUP 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. Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

1. Reply I need help.

I have two criteria data points to reference, to pull a third.

If A and B match on sheet 1 & 2, I need to pull in the third column’s data. How do I accomplish this? I believe I am overthinking this formula nesting.

• Reply Osman Goni Ridwan Aug 21, 2022 at 10:43 AM

https://www.exceldemy.com/excel-vlookup-multiple-criteria-without-helper-column/

Try the methods mentioned in this article and let us know the outcome. Thank you!  5 Excel Hacks You Never Knew  