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

## Download Practice Workbook

You can download our practice workbook from here for free!

## 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***, and**

*Total Amount***.**

*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,
will return the value of**VLOOKUP(C16,$C$5:$D$14,2, FALSE)**column for*Quantity***cell C16**. - To learn more about this
**VLOOKUP**function, you visit this**link** - 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. - To learn more about the
**IF**function, you can visit this**link**

**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 (3 Criteria)**

### 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
column using the*Market 1*part.**VLOOKUP(C16,B5:E14,3,FALSE)** - Otherwise, it will extract the price from the
column using*Market 2*this subformula.**VLOOKUP(C16,B5:E14,4,FALSE)**

- 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.this part will return the price of the entered ID and will be compared with the predefined maximum value.**VLOOKUP(C16,$B$5:$G$14,4)** - 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.

**Read More:** **How to Return the Highest Value Using VLOOKUP in Excel**

**Similar Readings**

**VLOOKUP Not Working (8 Reasons & Solutions)****INDEX MATCH vs VLOOKUP Function (9 Examples)****VLOOKUP to Return Multiple Columns in Excel (4 Examples)****VLOOKUP and Return All Matches in Excel (7 Ways)****Excel VLOOKUP to Return Multiple Values Vertically**

### 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. - If you want to explore more about this
**ISNA function**, you can visit this**link**.

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

**Read More:** **How to Use IF ISNA Function with VLOOKUP in Excel (3 Examples)**

### 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:**

, this part will check if the**VLOOKUP(C16,$B$5:$F$14,4,FALSE )>800****C16**cell’s lookup value in thecolumn is greater than 800.*Unit Price*, 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%.**=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%)**

- 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:**

, this part finds the unit price from the Unit Price column for the product ID that is in**VLOOKUP(C16,$B$5:$F$14,4,FALSE)****cell C16**., this part checks if the desired unit price value is available in the dataset or not.**ISNA(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.**=IF(ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)),”Not found”,VLOOKUP(C16,$B$5:$F$14,4,FALSE))**

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

**Read More:** **Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)**

### 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:**

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

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

And, visit **ExcelDemy **to learn more things about Excel! Have a nice day! Thank you!

**Further Readings**

**Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel****10 Best Practices with VLOOKUP in Excel****VLOOKUP Partial Match Multiple Values (3 Approaches)****VLOOKUP To Compare Two Lists in Excel (2 or More Ways)****VLOOKUP to Search Text in Excel (4 Easy Ways)****VLOOKUP Max of Multiple Values (With Alternative)**

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.

Hello APRIL, We already have an article written based on your problem. I hope, you will find this helpful. Follow this link below-

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!