If Statement with Vlookup
Learn how to write an IF statement with VLOOKUP to test lookup results, catch #N/A errors with IFERROR or IFNA, and replace the whole pattern with XLOOKUP.
Introduction
VLOOKUP finds a value; IF decides what to do with it. Wrapped together they turn a raw lookup into a label, a flag or a tier, and IFERROR keeps the ugly #N/A out of the report. This guide covers the patterns you will actually use, plus the XLOOKUP one-liner that replaces most of them.
Prerequisites
- Basic VLOOKUP syntax with the final FALSE argument
- Understanding of what a logical TRUE / FALSE test returns
- Microsoft 365, Excel 2021 or Google Sheets for the XLOOKUP section
1How IF and VLOOKUP Fit Together
VLOOKUP returns a value, and IF turns that value into a decision. The classic shape is IF(VLOOKUP(...) > threshold, "A", "B") — the lookup supplies the number, IF supplies the label. The sample table used here has SKU in A2:A6, product name in B, unit price in C and an In Stock flag in D: P-100 Widget 25 Yes, P-200 Gadget 40 No, P-300 Gizmo 15 Yes, P-400 Doohickey 60 Yes, P-500 Sprocket 12 No.
Get the value first
Write the VLOOKUP on its own and confirm it returns the number you expect. Column 3 is Unit Price in this table.
=VLOOKUP("P-300",$A$2:$D$6,3,FALSE)Wrap it in the test
Drop the whole lookup into IF's first argument and add the two outcomes. Add the exit strategy before the IF, not after.
=IF(VLOOKUP("P-300",$A$2:$D$6,3,FALSE)>20,"Premium","Budget")Example
=IF(VLOOKUP("P-300",$A$2:$D$6,3,FALSE)>20,"Premium","Budget")P-300 is the Gizmo at 15. The lookup returns 15, the test 15 > 20 is FALSE, so IF returns the second value, "Budget".
Always keep FALSE (or 0) as the last VLOOKUP argument. TRUE allows an approximate match and can quietly return a neighbouring row.
Excel evaluates every argument before running IF, so a failing VLOOKUP still produces #N/A even when IF's other branch would have hidden it.
2Testing the Lookup Result Instead of a Number
IF works just as well on text, which is how you convert a status column into readable output. Compare the lookup result against a literal and let IF choose the wording — useful for order statuses, compliance flags and availability checks.
Example
=IF(VLOOKUP("P-200",$A$2:$D$6,4,FALSE)="Yes","In stock","Out of stock")P-200 is the Gadget and column 4 holds "No", so the equality test is FALSE and IF returns the second value. Change the SKU to P-100 and the same formula returns "In stock".
Text comparisons are case-insensitive in Excel and Google Sheets. Wrap the test in EXACT if case matters.
Keep the two outcome strings the same data type — mixing text and numbers in the branches makes the column hard to sort.
3Catching #N/A with IFERROR or IFNA
An exact-match VLOOKUP that finds nothing returns #N/A, and IF cannot rescue it because the error happens before IF's test is evaluated. The fix is to wrap the lookup itself, not the test. IFERROR hides every error; IFNA hides only #N/A and lets genuine mistakes such as #REF! stay visible.
Wrap the lookup
IFERROR takes the entire VLOOKUP as its first argument and the fallback as its second.
=IFERROR(VLOOKUP("P-999",$A$2:$D$6,3,FALSE),"Not found")Narrow it down with IFNA
IFNA behaves identically for missing keys but leaves other errors on screen for you to fix.
=IFNA(VLOOKUP("P-999",$A$2:$D$6,3,FALSE),"Not found")Combine with a test
Once the error is handled, IF can safely work on the result.
=IF(IFERROR(VLOOKUP("P-999",$A$2:$D$6,3,FALSE),0)>20,"Premium","Budget")Example
=IFERROR(VLOOKUP("P-999",$A$2:$D$6,3,FALSE),"Not found")P-999 is absent from column A, so VLOOKUP returns #N/A and IFERROR substitutes the fallback text instead of letting the error spill into the report.
IFNA exists in Excel 2013 and later, Microsoft 365 and Google Sheets — it is the better default when the only expected failure is a missing key.
Do not use an empty string as the fallback for data you still need to audit; a visible "Not found" is worth more than a blank cell.
4Nested IF and IFS for Tiering Lookups
When one lookup has to map to several bands, nest IF or switch to IFS. IFS reads its conditions top to bottom and returns the value for the first TRUE test, so ordering is everything — put the highest threshold first.
Example
=IF(VLOOKUP("P-400",$A$2:$D$6,3,FALSE)>=50,"A",IF(VLOOKUP("P-400",$A$2:$D$6,3,FALSE)>=20,"B","C"))P-400 costs 60, which clears the first threshold, so the outer IF returns "A" and the inner IF never has to run. P-100 at 25 would fall through to "B", and P-500 at 12 all the way to "C".
IFS has no built-in else branch — close the list with TRUE,"Otherwise" or it returns #N/A when no condition matches.
Beyond three bands a lookup table beats nesting: an approximate-match VLOOKUP against a small band table is far easier to maintain than four stacked IFs.
5The XLOOKUP Shortcut and Cross-App Notes
XLOOKUP folds the lookup and the error handling into a single function: the fourth argument is what to return when nothing matches. It ships with Excel 2021, Microsoft 365 and Google Sheets, and it removes the need to wrap anything in IFERROR. The older IF + VLOOKUP pattern still matters on Excel 2019 and earlier, where XLOOKUP does not exist.
Example
=XLOOKUP("P-999",$A$2:$A$6,$C$2:$C$6,"Not found")The SKU is missing from the lookup range, so XLOOKUP returns the if_not_found argument instead of #N/A — one function where VLOOKUP needed IFERROR around it.
XLOOKUP can look left or right, so the return column may sit before the key — something VLOOKUP can never do.
Google Sheets supports VLOOKUP, IF, IFERROR, IFNA, IFS and XLOOKUP with the same argument order, so these formulas port across unchanged.
Functions Used
IF
IF performs a logical test and returns one value if true and another if false.
VLOOKUP
VLOOKUP (Vertical Lookup) is a built-in function in Excel and Google Sheets that searches for a value in the first column of a table and returns a value in the same row from a specified column.
IFERROR
IFERROR returns a custom result when a formula generates an error, and the formula result otherwise.
XLOOKUP
XLOOKUP is the modern replacement for VLOOKUP. It searches a range or array for a value and returns the corresponding item from a second range or array. It supports exact and approximate matching, wildcards, and can search in any direction.
Related Guides
IFERROR vs IFNA
IFERROR vs IFNA: which error handler should wrap your formulas? Compare what each one catches, with lookup and division examples for Excel and Google Sheets.
IF with AND / OR
Combining IF with AND OR logic lets a single formula test several conditions at once. Learn the syntax, nesting order and Excel and Google Sheets examples.
Sum Only Visible Cells Filtered
Learn how to sum only visible cells filtered data with SUBTOTAL codes 9 and 109, why a plain SUM keeps hidden rows, and the SUMPRODUCT criteria trick.
Summary
Wrapping VLOOKUP in IF turns a bare lookup into a decision: compare the result to a threshold or a text literal, and put IFERROR or IFNA around the lookup itself so a missing key becomes readable text instead of #N/A. For anything multi-banded, IFS or a band table beats stacked IFs, and on Excel 2021, Microsoft 365 or Google Sheets XLOOKUP replaces the whole combination with one call.
Next Steps
- Rewrite one existing VLOOKUP as IF(VLOOKUP(...)=..., ...) and check the fallback wording
- Swap IFERROR for IFNA where a missing key is the only realistic failure
- Try =XLOOKUP(key, lookup_range, return_range, "Not found") and compare it with your nested IF version