Home » Excel Functions » IF and VLOOKUP Nested Function in Excel: 5 Practical Uses and 2 Ways of Handling Errors

IF and VLOOKUP Nested Function in Excel: 5 Practical Uses and 2 Ways of Handling Errors

Avatar photo
Written by Md. Abdullah Al Murad
Last updated: Jun 16, 2024

Practical Uses

1. How to Match VLOOKUP Output with a Specific Value

Let’s say we want to determine how much inventory we have for a particular product.

Sample Dataset to Lookup a Value by Using IF and VLOOKUP Nested Function

Steps:

  • Select C17 and enter the following formula:
=IF(VLOOKUP(C16,$C$5:$D$14,2,FALSE)=0,"No","Yes")
  • Press Enter.

IF and VLOOKUP Nested Function Formula to Lookup a Value in Excel

Formula Breakdown:

  • VLOOKUP in C16 identifies Name as the search keyword.
  • $C$5:$D$14 identifies the search range; the 2 means we are looking for matching criteria in the second column (Quantity), while FALSE means we have an exact match.
  • The formula VLOOKUP(C16,$C$5:$D$14,2, FALSE) calculates the Quantity of product assigned to that
  • With the addition of the IF function, i.e. depending on whether the result is greater than zero, C17 indicates either Yes (the product is in stock) or No (the product is not currently in stock).

Note that if the product in C16 indicates a quantity greater than zero in D16, the result appears as Yes.

Vlookup Output with a Specific Value

Note that if the product in C16 indicates a quantity equal to zero in D16, the result appears as No.

Vlookup a Specific Value

We now know that the Apple iPhone X is not in stock.

Learn More: VLOOKUP and IF

 

Read More: How to Use Nested VLOOKUP in Excel 

2. How to Use the IF & VLOOKUP Nested Function With Two Lookup Values

Let’s say we want to locate the price of a particular product in a particular market.

Sample Dataset to Lookup Based on Two Values

Steps:

  • Select C18 and enter the following formula:
=IF(C17="Market 1",VLOOKUP(C16,B5:E14,3,FALSE),VLOOKUP(C16,B5:E14,4,FALSE))
  • Press Enter.

If and VLOOKUP Nested Function to Lookup Based on Two Values

  • Select C16 and enter the product’s ID.
  • Select C17 and enter Market 1.
  • Press Enter.

Formula Breakdown:

  • IF(C17=”Market 1″) determines that our initial interest is in Apple iPhone X’s Market 1 price.
  • VLOOKUP(C16,B5:E14,3,FALSE) identifies the search range, i.e. the third column (Market 1).
  • IF(C17=”Market 1″,VLOOKUP(C16,B5:E14,4,FALSE) means that if there is no Market 1 price, the search moves on to the fourth column (Market 2).
  • When the Apple iPhone X’s ID is entered in C16 and “Market 1” in C17, the price will appear in C18.

Looked Up Two Values with IF and VLOOKUP Nested Function

We now know that the Market 1 price for the Apple iPhone X is $1,150.00.

Read More: Excel LOOKUP vs VLOOKUP

3. How to Match Lookup Returns with Another Cell Using the MAX Function

Let’s say we want to compare unit prices across products to see which is the highest.

Sample Dataset to Return Vlookup through Another Cell

Steps:

  • Select C17 and enter the following formula:
=IF(VLOOKUP(C16,$B$5:$G$14,4)>=F16,"Yes","No")
  • Press Enter.

IF VLOOKUP Nested Formula to Match Lookup Through Another Cell

Formula Breakdown:

  • VLOOKUP(C16,$B$5:$G$14,4) compares the Apple iPhone 11 Pro’s Unit Price with that of the highest price (calculated in Example 3).
  • IF >=F16,”Yes”,”No” determines whether that price is greater than or equal to the price in F16.  
  • IF(VLOOKUP(C16,$B$5:$G$14,4) >=F16,”Yes”,”No” compares these prices, then indicates Yes or No in C17.

Looked Up Through Another Cell

We now know that the most expensive product we sell is the Apple iPhone 11 Pro.

Read More: Return the Highest Value Using VLOOKUP Function in Excel

4. How to Use the IF & VLOOKUP Nested Function to Lookup Values from a Shorter List

Let’s say we want to find out whether a particular product has been delivered.

Sample Dataset to Lookup Values from A Shortlist

Steps:

  • Select G5 and enter the following formula:
=IF(ISNA(VLOOKUP(C5,$I$5:$I$10,1,FALSE)),"Not Delivered","Delivered")
  • Press Enter.

IF and VLOOKUP Nested Formula to Lookup Values from Shortlist

Formula Breakdown:

  • IF establishes the delivery status of each product as Delivered or Not delivered
  • ISNA sets the criterion as TRUE (if delivered) or FALSE (if not).
  • VLOOKUP(C5,$I$5:$I$10,1, FALSE) checks the Name of each product and, if it matches TRUE, adds it to the Delivered Project List (column I), then indicates Delivered or Not delivered in G5.

To duplicate the formula, click and drag the Fill Handle down the targeted range.

Looked Up Values from Shortlist

We now know that six of the ten products have been delivered.

5. How to Use the IF & VLOOKUP Nested Function to Perform Different Calculations

Let’s say we want to find out whether a) with a discount of 20%, the unit price is greater than $800 or b) with a discount of 15%, it’s lower than $800.

Sample Dataset to Perform More Calculations During Lookup

Steps:

  • Select 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%)
  • Press Enter.

If VLOOKUP Nested Function to Perform Different Calculations

Formula Breakdown:

  • IF establishes that the Unit Price is either over or under 800.
  • VLOOKUP(C16,$B$5:$F$14,4,FALSE )>800 checks whether the product ID entered in C16 has a Unit Price 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%) ensures that the product’s unit price is correctly multiplied by 15% (if greater than 800) or  20% (if less than 20%), ), then indicates the Discount in C17.

Performed Different Calculations with IF and VLOOKUP Nested Function

We now know the discounted price for the Apple iPhone 11 Pro is $180 less than its unit price.

Handling Errors

Sometimes there’s no match to your lookup, so you might get #N/A or 0.

Sample Dataset to Handle #N/A Errors

1. How to Use the ISNA Function with the IF & VLOOKUP Nested Function to Hide #N/A Errors

Steps:

  • Select C17 and enter the following formula:
=IF(ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)),"Not found",VLOOKUP(C16,$B$5:$F$14,4,FALSE))
  • Press Enter.

ISNA Formula to Handle #N/A Errors

Formula Breakdown:

  • IF establishes that each product in the dataset may or may not have a unit price.
  • VLOOKUP(C16,$B$5:$F$14,4,FALSE) searches Unit Price (column E) for the product ID entered in C16.
  • ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)) checks whether or not the product has a unit price.
  • =IF(ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)),”Not found”,VLOOKUP(C16,$B$5:$F$14,4,FALSE)) ensures C17 will indicate either the unit price (if the product has one) or “Not found” (if it doesn’t).

Avoided #N/A Error

2. How to Use the ISNA Function with the IF & VLOOKUP Nested Function to Represent Missing Data as 0

Steps:

  • Select C17 and enter the following formula:
=IF(ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)),0,VLOOKUP(C16,$B$5:$F$14,4,FALSE))
  • Press Enter.

Formula to Return 0 Wen Lookup Data Doesn't Match

Formula Breakdown:

  • IF establishes that each product may or may not have a unit price.
  • ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)) searches Unit Price (column E) for the product ID entered in C16.
  • =IF(ISNA(VLOOKUP(C16,$B$5:$F$14,4,FALSE)),0,VLOOKUP(C16,$B$5:$F$14,4,FALSE)) ensures C17 indicates either the unit price (if the product has one) or 0 (if it doesn’t).

Returned 0 Instead of Errors

Things to Remember

#N/A errors typically appear because:

  • The lookup value does not exist in the table.
  • The lookup value is misspelled or contains extra space.
  • The table range was not entered correctly.
  • You are copying VLOOKUP across several sells without first locking the table reference.

When cells are formatted as currently, there will be a dashed line (-) instead of 0.

Download Free Practice Workbook

Further Readings

<< Go Back to VLOOKUP with IF Condition | Excel VLOOKUP Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

2 Comments
  1. Reply
    April Jul 25, 2022 at 9:59 PM

    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.

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

ExcelDemy Consulting Services

Submit Excel Task

 

See Our Review attrustpilot review

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo