VLOOKUP with IF Statement: How to Combine Them

Formulas & functions · Updated November 22, 2026

To combine VLOOKUP with an IF statement in Excel, you nest the VLOOKUP inside the IF function, like this: =IF(VLOOKUP(A2, B:C, 2, FALSE) > 100, "High", "Low"). This checks the lookup result against a condition and returns one value if true, another if false. The key is that VLOOKUP runs first, returning a value, and then IF evaluates that value against your logical test.

Why Combine VLOOKUP and IF?

VLOOKUP alone returns whatever value is in the target column—it cannot make decisions. IF adds conditional logic, so you can categorize, flag, or transform the lookup result. Common uses include: turning numeric scores into letter grades, marking inventory as “In Stock” or “Out of Stock”, or applying a discount only when a threshold is met. Without IF, you would need a separate helper column or manual review.

For example, suppose you have a product price list in columns A (product) and B (price). You want to label each product as “Budget” if under $50, otherwise “Premium”. The formula in C2 would be:

=IF(VLOOKUP(A2, A:B, 2, FALSE) < 50, "Budget", "Premium")

This works because VLOOKUP finds the price for the product in A2, then IF compares it to 50.

How to Write the Combined Formula

Follow these steps in Excel for Microsoft 365 on Windows (similar on Mac):

  1. Click the cell where you want the result.
  2. Type =IF( to start the IF function.
  3. Inside the parentheses, type your VLOOKUP as the logical test. The logical test must be a comparison, so add an operator like >, <, =, <>, or a function like ISNUMBER.
  4. Add a comma, then the value if true, another comma, then the value if false.
  5. Close both parentheses: one for IF, one for VLOOKUP. The order is =IF(VLOOKUP(...), value_if_true, value_if_false).

Example with text result: =IF(VLOOKUP(D2, A:B, 2, FALSE) = "Yes", "Active", "Inactive"). Here, VLOOKUP returns a text value, and IF checks if it equals “Yes”.

Mac users: the formula syntax is identical; Excel on Mac uses the same comma separators and function names.

Using IFERROR with VLOOKUP (A Common Pairing)

Often you need IFERROR to handle #N/A errors when VLOOKUP finds no match. The combination is: =IFERROR(VLOOKUP(A2, B:C, 2, FALSE), "Not Found"). This is not the same as using IF with VLOOKUP, but many users ask about it. IFERROR catches any error from VLOOKUP and returns a custom message. You can also nest IFERROR inside IF: =IF(IFERROR(VLOOKUP(A2, B:C, 2, FALSE), 0) > 100, "High", "Low"). Here, if VLOOKUP errors, it becomes 0, so the IF test evaluates false.

Remember: IFERROR is available in all modern Excel versions, but if you are using Excel 2007 or earlier, use =IF(ISERROR(VLOOKUP(...)), "Not Found", VLOOKUP(...)).

How to Choose the Right Logical Test

Your IF condition must make sense with the data type VLOOKUP returns. If VLOOKUP returns a number, use numeric comparisons like >=. If it returns text, use = with quotes. If it returns a date, compare with a date value like DATE(2025,1,1).

  • Numeric example: =IF(VLOOKUP(A2, B:C, 2, FALSE) >= 1000, "High", "Low")
  • Text example: =IF(VLOOKUP(A2, B:C, 2, FALSE) = "Pending", "Review", "OK")
  • Date example: =IF(VLOOKUP(A2, B:C, 2, FALSE) > DATE(2025,1,1), "New", "Old")

Be careful with text case: VLOOKUP is not case-sensitive, so “yes” and “YES” are treated as equal. If you need case-sensitive matching, use INDEX/MATCH with EXACT instead.

Common Problems and How to Fix Them

Here are frequent issues when combining VLOOKUP and IF:

  • #N/A error: VLOOKUP cannot find the lookup value. Use IFERROR to handle it, or check that the lookup column is the first column in the table array.
  • #VALUE! error: The IF logical test is not a valid comparison, or VLOOKUP returns text when you compare with a number. Ensure the data types match.
  • Wrong results due to approximate match: If you omit the fourth argument (range_lookup) or set it to TRUE, VLOOKUP uses approximate match. Always use FALSE for exact match unless you have sorted data and need closest match.
  • Column index out of range: The column index number must be less than or equal to the number of columns in the table array. If your table is A:C and you ask for column 4, you get #REF!.
  • IF returns only the first condition: If you need multiple conditions, use nested IFs: =IF(VLOOKUP(...) > 100, "High", IF(VLOOKUP(...) > 50, "Medium", "Low")). But this repeats VLOOKUP; consider using LET to define it once (Microsoft 365 only).

For Microsoft 365, you can use LET to avoid repeating VLOOKUP: =LET(price, VLOOKUP(A2, B:C, 2, FALSE), IF(price > 100, "High", "Low")). This is cleaner and faster.

Alternatives to VLOOKUP with IF

VLOOKUP has limitations: it only looks left-to-right, and it returns only one column. For more flexibility, use INDEX/MATCH: =IF(INDEX(C:C, MATCH(A2, B:B, 0)) > 100, "High", "Low"). This allows lookup columns to the left of the return column. In Excel 2021 or Microsoft 365, XLOOKUP is even simpler: =IF(XLOOKUP(A2, B:B, C:C) > 100, "High", "Low"). XLOOKUP defaults to exact match and handles errors natively with its fourth argument.

Another alternative is using IF with COUNTIF to check existence: =IF(COUNTIF(B:B, A2) > 0, "Exists", "Missing"). This does not return a value from another column, but it checks if a lookup value exists.

Frequently Asked Questions

Can I use VLOOKUP with multiple IF conditions? Yes, you can nest multiple IFs, but it gets messy. For example, =IF(VLOOKUP(A2, B:C, 2, FALSE) > 90, "A", IF(VLOOKUP(A2, B:C, 2, FALSE) > 80, "B", "C")). For more than a few conditions, consider using a lookup table with approximate match or the IFS function (Excel 2019 and later).

Why does my VLOOKUP return #N/A even though the value exists? This usually happens when the lookup value is a number stored as text, or there are extra spaces. Use TRIM and VALUE functions, or ensure both columns have the same data type. Also check that the lookup column is the first column in the table array.

Can I use IF with VLOOKUP to return a value from another sheet? Yes, just reference the other sheet in the table array, like =IF(VLOOKUP(A2, Sheet2!B:C, 2, FALSE) > 100, "High", "Low"). Make sure the sheet name is spelled correctly and quoted if it has spaces.

What is the difference between IFERROR and IF with VLOOKUP? IFERROR catches any error from VLOOKUP and returns an alternative. IF with VLOOKUP checks a condition on the result. They serve different purposes: IFERROR handles missing matches; IF categorizes results. You can combine them: =IF(IFERROR(VLOOKUP(A2, B:C, 2, FALSE), 0) > 100, "High", "Low").

Does VLOOKUP work with wildcards in IF? Yes, VLOOKUP supports wildcards (* and ?) when using exact match (FALSE). For example, =IF(VLOOKUP("*"&A2&"*", B:C, 2, FALSE) = "Yes", "Match", "No"). This finds the first cell in column B that contains the text in A2.

Is there a performance issue with combining VLOOKUP and IF? In large datasets, VLOOKUP is slower than INDEX/MATCH or XLOOKUP. If you have thousands of rows and use VLOOKUP inside IF, it recalculates each time. Using LET to define VLOOKUP once (Microsoft 365) or switching to XLOOKUP improves speed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.