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.


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, Total Amount, and Delivery Date.

Sample Dataset to Lookup a Value by Using IF and VLOOKUP Nested Function

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

IF and VLOOKUP Nested Function Formula to Lookup a Value in Excel

🔎 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.
  • 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
  •  Now, enter any name of the product which has a quantity greater than zero on cell C16 and check the output.

Vlookup Output with a Specific Value

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

Vlookup a Specific Value

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.

Sample Dataset to Lookup Based on Two Values

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

If and VLOOKUP Nested Function to Lookup Based on Two Values

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

Looked Up Two Values with IF and VLOOKUP Nested Function

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.

Sample Dataset to Return Vlookup through Another Cell

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

IF VLOOKUP Nested Formula to Match Lookup Through Another Cell

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

Looked Up Through Another Cell

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


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.

Sample Dataset to Lookup Values from A Shortlist

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

IF and VLOOKUP Nested Formula to Lookup Values from Shortlist

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

Looked Up Values from Shortlist

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.

Sample Dataset to Perform More Calculations During Lookup

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

If VLOOKUP Nested Function to Perform Different Calculations

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

Performed Different Calculations with IF and VLOOKUP Nested Function

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.

Sample Dataset to Handle #N/A Errors


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.

ISNA Formula to Handle #N/A Errors

🔎 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.
  • Consequently, you will get your desired result as Not found in cell C17 instead of the #N/A error.

Avoided #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 to Return 0 Wen Lookup Data Doesn't Match

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

Returned 0 Instead of Errors

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.

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


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

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.

2 Comments
  1. 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.

Leave a reply

ExcelDemy
Logo