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 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 different uses of the IF and VLOOKUP nested functions in Excel.
Download the Practice Workbook
7 Ways to Use IF and VLOOKUP Nested Function in Excel
1. Match 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 will find out the availability of each product using the IF and VLOOKUP formula. We will search the availability of the product by entering the name.
Step 1: Enter the following formula in cell C19 and press Enter
=IF(VLOOKUP(C18,$C$4:$D$15,2,FALSE)=0,"No","Yes")
Formula Explanation
 Firstly, in the VLOOKUP function C18 is the cell which is holding the search keyword. Then $C$4:$D$15 is the range where we will search the entered data. 2 is used as we are considering the first 2 columns of the data and lastly FALSE is used to define the exact match.
 So, VLOOKUP(C18,$C$4:$D$15,2, FALSE) will return either TRUE or FALSE (0 or 1) as a result.
 To learn more about this VLOOKUP function, you visit this link
 Then the IF function is checking whether the output of the VLOOKUP function is 0 or 1. 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
Step 2: Now enter any name of the product and check the output
Step 3: Enter any name where the quantity is 0
2. Lookup Based on Two Values Using IF and VLOOKUP Nested Function
Now we will search any elements or products based on two values using IF and VLOOKUP nested functions. In the dataset, there are two different market prices for each product. Here are two values: a product id and market number. We will find the product’s price based on these two values.
Step 1: Enter the following formula in cell C20 and press Enter
=IF(C19="Market 1",VLOOKUP(C18,B4:E15,3,FALSE),VLOOKUP(C18,B4:E15,4,FALSE))
Formula Explanation
 In the IF function C19=”Market 1″ is the logical condition. It is checking whether the entered Market No is the same or not.
 If the Market No is 1 then the price will be extracted from the Market 1 column using the VLOOKUP(C18, B4:E15,3, FALSE) part.
 Otherwise, it will extract the price from the Market 2 column using VLOOKUP(C18, B4:E15,4, FALSE) this sub formula.
Step 2: First enter ID then Market No and then press Enter
3. Match VLOOKUP Returns with Another Cell
No in this part we will find out the highest price from the data and compare if our searched data is matched with the enter data or not. The highest salary will be predefined.
Step 1: Enter the following formula in cell C19 and press Enter
=IF(VLOOKUP(C18,$B$4:$G$15,4)>=C23, "Yes!", "No")
Formula Explanation
 In the IF function firstly we are checking the condition using the VLOOKUP function’s return value.VLOOKUP(C18,$B$4:$G$15,4) this part will return the price of the entered ID and will be compared with the predefined maximum value.
 If the product ID is matched with the entered ID, then it will print Yes, otherwise it will print No.
Step 2: Enter any ID and check the output
4. VLOOKUP Values in a Shorter List Using IF and VLOOKUP Nested Function
Now we will sort or extract some specific data from a list using IF and VLOOKUP nested functions. Let’s assume an additional column of 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 Delivered Product List table information.
Step 1: Enter the following formula in cell G4 and copy it down up to G15
=IF(ISNA(VLOOKUP(C4,$C$18:$C$23,1,FALSE)),"Not Delivered","Delivered")
Formula Explanation
 Additionally, we have used the ISNA function here. If the ISNA function could find the matched data name from the then it will return TRUE, otherwise FALSE.
 If you want to explore more about this ISNA function, you can visit this link
 Using the return value of the ISNA function, the IF function prints either “Not Delivered” or “Delivered”.
5. Excel IF VLOOKUP Nested Function to Perform Different Calculations
Now in this section, we will 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. Again, we will use the same dataset above.
Step 1: Enter the following formula in cell C19 and press Enter
Step 2: Now enter any ID and see the output
6. IF ISNA VLOOKUP to Hide #N/A Errors
If the searched value is not present in the dataset, then VLOOKUP will return a #NA error. To prevent this type of error we can use the ISNA function with IF and VLOOKUP functions. Let’s see how to do that:
Step 1: Enter the following formula in cell C19 and press Enter
=IF(ISNA(VLOOKUP(C18,$B$4:$F$15,4,FALSE)), "Not found", VLOOKUP(C18,$B$4:$F$15,4,FALSE))
Step 2: Now enter the ID which is not in the dataset
7. Return 0 for Missing Data Using IF and VLOOKUP Function
Now here instead of returning “Not found” we will print the price as 0. Again, the dataset will be the same.
Step 1: Enter the following formula in cell C19 and press Enter
=IF(ISNA(VLOOKUP(C18,$B$4:$F$15,4,FALSE)), 0, VLOOKUP(C18,$B$4:$F$15,4,FALSE))
Step 1: Now again enter the ID which is not in the dataset
[ 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 dash line ().]
Things to Remember
#N/A in VLOOKUP  In practice, there are many reasons why you might see this error, including:

Conclusion
These are some ways to use the IF and VLOOKUP nested function in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.