# IF and VLOOKUP Nested Function (7 Ways) 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.

Read more: VLOOKUP to Search Text in Excel

## 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.
• 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. 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 pre-defined. 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.
• 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

=IF(VLOOKUP(C18,\$B\$4:\$F\$15,4,FALSE )>800, VLOOKUP(C18,\$B\$4:\$F\$15,4,FALSE)*15%, VLOOKUP(C18,\$B\$4:\$F\$15,4,FALSE)*20%) 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: 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

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.  