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):
- Click the cell where you want the result.
- Type
=IF(to start the IF function. - 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. - Add a comma, then the value if true, another comma, then the value if false.
- 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
- Excel to Notepad Converter Online: How to Convert
- Excel Timesheet Template for Multiple Projects
- Excel Templates for Time Tracking: Best Free Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Capitalize Names in Excel: Formulas and MethodsTo capitalize names in an Excel column, enter =PROPER(A2) in the next column, replacing A2 with the first…
- Accumulated Depreciation Formula in ExcelFor straight-line depreciation, accumulated depreciation is the depreciation recorded to date: annual depreciation multiplied by the number of…
- How to Filter in Microsoft Excel: A Practical GuideTo filter in Microsoft Excel, select a cell in your data, then choose Data > Filter or press…
- Excel Import CSV: Formula or the Right Import MethodExcel does not have a built-in worksheet formula that imports a CSV file from your computer. In Microsoft…
- Excel Query Function: What to Use InsteadExcel does not have a worksheet function named QUERY like Google Sheets does. In Microsoft 365 for Windows,…
- Excel Formula to Extract the First Five Digits from D2Assuming D2 contains the text or number you want to search, use =IFERROR(IF(D2="","",LEFT(TEXTJOIN("",TRUE,IFERROR(MID(D2,SEQUENCE(LEN(D2)),1)*1,"")),5)),"") to collect the first five…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.