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