This tutorial demonstrates how to combine the VLOOKUP and IF functions in Excel to perform lookups with conditional logic. You'll also learn how to handle #N/A errors using IF ISNA and IFNA with VLOOKUP. This assumes familiarity with the syntax of both functions.

• Combining IF and VLOOKUP for True/False or Yes/No results
• Using IF and VLOOKUP for conditional calculations
• Handling #N/A errors with IF ISNA and VLOOKUP
• Using IFNA and VLOOKUP (Excel 2013 and later)
• Returning 0 or a blank cell when VLOOKUP fails
• Using IF with INDEX MATCH for left lookups

Conditional VLOOKUP: True/False, Yes/No, etc.

A common use case is comparing a VLOOKUP result to a value and returning Yes/No or True/False. A general formula is:

IF(VLOOKUP(...) = value, TRUE, FALSE)

This returns True if the VLOOKUP result equals the specified value, otherwise False.

Example 1: Checking Stock Availability

Suppose column A contains items and column B their quantities. A formula to check if an item (in cell E1) is in stock is:

=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,"No","Yes")

This uses VLOOKUP to find the quantity and IF to check if it's zero. You can easily adapt this to return "In Stock/Sold Out" or other alternatives. You can also compare against text values (enclosed in quotes).

Example 2: Comparing VLOOKUP Result to Another Cell

You can compare the VLOOKUP output to a value in another cell. For example, to check if it's greater than or equal to a number in cell G2:

=IF(VLOOKUP(E1,$A$2:$B$10,2)>=G2,"Yes!","No")

Example 3: VLOOKUP in a Smaller List

To check if a value (in column A) exists in a smaller list (D2:D4), use:

IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),"No","Yes")

This returns "No" if #N/A (value not found), and "Yes" otherwise. Swap "Yes" and "No" to reverse the logic.

Conditional Calculations with VLOOKUP

IF and VLOOKUP can perform different calculations based on criteria. For example, calculating commission (20% for sales >= $200, 10% otherwise):

=IF(VLOOKUP(F1,$A$2:$C$10,3,FALSE)>=200,VLOOKUP(F1,$A$2:$C$10,3,FALSE)*20%,VLOOKUP(F1,$A$2:$C$10,3,FALSE)*10%)

Handling #N/A Errors with IF ISNA and VLOOKUP

To replace #N/A errors with custom text, use:

IF(ISNA(VLOOKUP(...)),"Not found",VLOOKUP(...))

IFNA VLOOKUP (Excel 2013 and later)

In newer Excel versions, use IFNA for a more concise solution:

IFNA(VLOOKUP(...),"Not found")

Returning 0 or Blank Cells

To return 0 instead of #N/A:

IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) or IFNA(VLOOKUP(...),0)

To return a blank cell:

IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)) or IFNA(VLOOKUP(...),"")

IF with INDEX MATCH

INDEX MATCH offers a more versatile alternative to VLOOKUP, especially for left lookups. Combine it with IF ISNA or IFNA to handle errors:

IF(ISNA(INDEX(A2:A10,MATCH(F1,$B$2:$B$10,0))),"Not found",INDEX(A2:A10,MATCH(F1,$B$2:$B$10,0))) or IFNA(INDEX(A2:A10,MATCH(F1,$B$2:$B$10,0)),"Not found")

A sample workbook is available for download (link omitted as I cannot provide files). This demonstrates various combinations of IF and VLOOKUP (and INDEX MATCH) to handle different scenarios effectively.

The above is the detailed content of IF VLOOKUP in Excel: Vlookup formula with If condition. For more information, please follow other related articles on the PHP Chinese website!