Nazmus Sakib
IF and VLOOKUP functions are individually very popular in Excel. Nonetheless, if we use IF and VLOOKUP functions together, we can do a lot of operations by implementing various conditions. In this article, we will show how to use IF and VLOOKUP nested function in Excel with 5 convenient examples.
📁 Download Excel File
Download the following Excel workbook to realize the topic more clearly.
Learn to Use IF and VLOOKUP as a Nested Function in Excel with These 5 Easy Examples
Today we will use the following sample dataset to learn how to use the IF and VLOOKUP nested function.
1. IF and VLOOKUP Nested Functions Based on Particular Value
In our first approach, we will learn how to use the VLOOKUP function to look up a value and the IF function will return Yes or No in accordance with the lookup value.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, select cell C14 and Enter the following formula:
=IF(VLOOKUP(B14,$B$6:$F$11,4,FALSE)="Available","Yes","No")
- Now, you can see the Availability of the Product Code stored in cell B14. As you can see from the picture below, we have entered Code M11 to see the Availability and it shows Yes.
🔨 Formula Breakdown
IF(VLOOKUP(B14,$B$6:$F$11,4,FALSE)=”Available”,”Yes”,”No”)
👉 The VLOOKUP function will look up the Code in cell B14 from cells B6 to F11 and return the corresponding value present in the Availability column. Here, FALSE denotes we want an exact match.
👉 The IF function will check if the value is Available. If the value is Available, it will return Yes, else it will return No.
2. IF and VLOOKUP Nested Functions Based on Multiple Values
Now, we will use multiple values to return the corresponding value using the IF and VLOOKUP nested function.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell C15 and type in the following formula, and press Enter:
=IF(C14="CA",VLOOKUP(C13,B6:F11,4,FALSE),VLOOKUP(C13,B6:F11,5,FALSE))
- Next, cell C15 will show the corresponding Price for the State and Product Code.
- Furthermore, You can insert TX to see the Price in TX just like in the photo below.
🔨 Formula Breakdown
IF(C14=”CA”,VLOOKUP(C13,B6:F11,4,FALSE),VLOOKUP(C13,B6:F11,5,FALSE))
👉 The IF function will check the value in cell C14.
👉 If it is CA, it will return the Price in CA corresponding to the Code in cell C13 from cells B6:F11 with the help of the VLOOKUP function. else it will return the congruous Price in TX.
3. IF, ISNA, and VLOOKUP Nested Functions Using Multiple Lists
In this approach, we will check the Availability of a product from a list of Available Items.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, we will Enter the following formula in cell F6:
=IF(ISNA(VLOOKUP(C6,$H$6:$H$9,1,FALSE)),"Not Available","Available")
- Next, use the Fill Handle icon and double-click to apply the formula.
- Voila! Our Availability column is showing the availability of the products according to the Available Items list.
🔨 Formula Breakdown
IF(ISNA(VLOOKUP(C6,$H$6:$H$9,1,FALSE)),”Not Available”,”Available”)
👉 The VLOOKUP function will look up the Item in cell C6. From cells H6 to H9, it will return the Item name.
👉 If both of the Values match, the ISNA function will return FALSE.
👉 The IF function will return Not Available if the ISNA function returns TRUE, else it will return Available.
4. Executing Calculations Utilizing IF and VLOOKUP Nested Functions
Now we will calculate the Selling Price using a condition using the IF and VLOOKUP nested functions.
⬇️⬇️ STEPS ⬇️⬇️
- First, Enter the following formula in cell C14:
=IF(VLOOKUP(B14,$B$6:$F$11,5,FALSE )>2.5, VLOOKUP(B14,$B$6:$F$11,5,FALSE)*1.15, VLOOKUP(B14,$B$6:$F$11,5,FALSE)*1.2)
- The formula will return the Selling Price according to the Code and corresponding Cost.
🔨 Formula Breakdown
IF(VLOOKUP(B14,$B$6:$F$11,5,FALSE )>2.5, VLOOKUP(B14,$B$6:$F$11,5,FALSE)*1.15, VLOOKUP(B14,$B$6:$F$11,5,FALSE)*1.2)
👉 The first VLOOKUP function will look up the value stored in cell B14 from cells B6:F11 and it will return the Cost of the product.
👉 The IF function will check if the Cost is greater than 2.5.
👉 If the Cost is greater than 2.5, the IF function will return 1.15 times the Cost with help of the second VLOOKUP function.
👉 Else the IF function will return 1.2 times the Cost as the Selling Price using the outcome of the third VLOOKUP function.
5. Applying IF and VLOOKUP Nested Functions Based on Multiple Conditions
At this time, we will show how to return value based on multiple conditions.
⬇️⬇️ STEPS ⬇️⬇️
- Select cell F13, and Enter the following formula:
=AVERAGE(F6:F11)
- We now have the Average Cost of the Items as we used the Average function.
- Later, we will Enter the formula below in cell C14:
=IF(VLOOKUP(C13,$B$6:$F$11,5)>=F13,"Above Average","Below Average")
- Now cell C14 shows if the cost is below the average cost or above.
🔨 Formula Breakdown
IF(VLOOKUP(C13,$B$6:$F$11,5)>=F13,”Above Average”,”Below Average”)
👉 The VLOOKUP function will look up the Code in cell C13 from the dataset and return the Cost of the corresponding Code.
👉 If the Cost is greater than the Average Cost in cell F13, the IF function will return Above Average. Otherwise, it will return Below average.
How to Use Nested VLOOKUP Function in Excel
Let’s see how to use the nested VLOOKUP function from the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Enter the following formula in cell G11:
=VLOOKUP(VLOOKUP(G10,B6:D11,3,FALSE),F6:G8,2,FALSE)
- Now cell G11 will show the Price based on the Size.
🔨 Formula Breakdown
VLOOKUP(VLOOKUP(G10,B6:D11,3,FALSE),F6:G8,2,FALSE)
👉 The second VLOOKUP function will look up the Code from cell G10 from cells B6:D11 and return the corresponding Size of the Code.
👉 The first VLOOKUP function will use the Size and look up from cells F6:G8 and return the Price of the Size in cell G11.
📝 Takeaways from This Article
📌 First, we learned how to use IF and VLOOKUP nested functions by utilizing a specific condition.
📌 Secondly, we used IF and VLOOKUP nested functions to return corresponding value for multiple cell references.
📌 Thirdly, we combined IF, ISNA, and VLOOKUP functions to return value for multiple lists.
📌 Later, we discussed how to use IF and VLOOKUP nested functions to perform calculations to the returned corresponding value.
📌 Afterward, we learned to use IF and VLOOKUP nested functions based on multiple conditions.
📌 Finally, we demonstrated how to use VLOOKUP nested function.
Conclusion
That concludes the discussion for today. These are some convenient methods to use IF and VLOOKUP nested functions to carry out different operations. If you have any queries or recommendations, kindly notify us in the comments section. Downloading the practice sheet will help you comprehend the concepts better. Visit our website ExcelDen to find out about diverse kinds of Excel methods. We appreciate you taking the time to read this article.
