IF and VLOOKUP Nested Function (7 Ways)

IF and VLOOKUP Nested Function

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

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.

IF and VLOOKUP Nested Function

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

IF and VLOOKUP Nested Function

Step 2: Now enter any name of the product and check the output

IF and VLOOKUP Nested Function

Step 3: Enter any name where the quantity is 0

IF and VLOOKUP Nested Function

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.

IF and VLOOKUP Nested Function

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.

IF and VLOOKUP Nested Function

Step 2: First enter ID then Market No and then press Enter

IF and VLOOKUP Nested Function

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.

IF and VLOOKUP Nested Function

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.

IF and VLOOKUP Nested Function

Step 2: Enter any ID and check the output

IF and VLOOKUP Nested Function

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.

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

IF and VLOOKUP Nested Function

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.

IF and VLOOKUP Nested Function

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

IF and VLOOKUP Nested Function

Step 2: Now enter any ID and see the output

IF and VLOOKUP Nested Function

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:

IF and VLOOKUP Nested Function

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

IF and VLOOKUP Nested Function

Step 2: Now enter the ID which is not in the dataset

IF and VLOOKUP Nested Function

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

IF and VLOOKUP Nested Function

Step 1: Now again enter the ID which is not in the dataset

IF and VLOOKUP Nested Function

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


Further Readings:

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo