How to Combine IF and VLOOKUP in Excel

To combine IF and VLOOKUP in Excel, put the VLOOKUP inside IF's test: VLOOKUP fetches a value from a table, and IF decides what to show based on it. A typical if vlookup formula is =IF(VLOOKUP(E2,A2:D6,3,FALSE)<20,"Reorder","OK"). This guide walks through the four patterns you'll actually use, on one small stock list, with the result of every formula.

How IF and VLOOKUP work together#

IF asks a yes-or-no question and returns one of two answers. VLOOKUP finds a row by its first column and returns a value from another column. Put them together and the question can be about a value that lives in another table.

=IF(VLOOKUP(lookup_value, table, column, FALSE) test, value_if_true, value_if_false)

Excel works from the inside out. First VLOOKUP returns a value, such as 18. Then IF checks the test, such as 18<20, and returns the matching answer.

All examples use this office-supplies list in A1:D6, with a code typed in E2:

If you need a refresher on either function first, read how the IF function works and how VLOOKUP finds a value.

Example 1: compare a looked-up number with a limit#

The most common job is to test a looked-up number. Here we flag items with fewer than 20 in stock:

=IF(VLOOKUP(E2,A2:D6,3,FALSE)<20,"Reorder","OK")

With P-104 in E2, VLOOKUP returns 18, so the result is Reorder. With P-101 it returns 45, so the result is OK.

Keep the number 20 out of quotes. VLOOKUP returns a real number, and Excel compares numbers with text in an odd way: any number counts as smaller than any text. So <"20" would be TRUE for every item, even the 120 pens.

To make the limit easy to change, put it in a cell, say G1, and write <G1 instead of <20. When you fill the formula down a list, lock the table and the limit with $: =IF(VLOOKUP(E2,$A$2:$D$6,3,FALSE)<$G$1,"Reorder","OK").

Example 2: check a looked-up text value#

IF can test text too. Put the text in double quotes after an equals sign:

=IF(VLOOKUP(E2,A2:D6,4,FALSE)="Discontinued","Do not order","Can order")

With P-105 in E2, VLOOKUP returns Discontinued, so the result is Do not order. The comparison ignores capital letters, so "discontinued" matches too. It does not ignore spaces: "Discontinued " with a trailing space won't match. The guide to IF with text conditions covers partial matches and other text tests.

Example 3: check whether a value exists in a list#

To test if a code is in the list, check whether VLOOKUP fails. VLOOKUP returns #N/A when it can't find a value, and ISNA turns that into TRUE:

=IF(ISNA(VLOOKUP(E2,A2:A6,1,FALSE)),"Not found","Found")

With P-999 in E2, the result is Not found. With P-102, it's Found. Here the table is just the code column and the column number is 1, because you only care whether the code is there.

A shorter way does the same job without VLOOKUP:

=IF(COUNTIF(A2:A6,E2)>0,"Found","Not found")

COUNTIF counts how many times the code appears. Anything above zero means it exists.

Example 4: avoid #N/A and blank-cell zeros#

When the code might be missing, the whole IF formula shows #N/A, because the error from VLOOKUP passes straight through IF. Wrap the formula in IFERROR to show a message instead:

=IFERROR(IF(VLOOKUP(E2,A2:D6,3,FALSE)<20,"Reorder","OK"),"Code not found")

With P-999 in E2, this returns Code not found. IFERROR hides every error, including typos in your own formula, so test the formula before you wrap it. The article on IFERROR with VLOOKUP compares it with IFNA, which hides only #N/A.

IF also fixes a second annoyance. When the cell VLOOKUP returns is empty, Excel shows 0 instead of a blank. Test for the empty result and return "":

=IF(VLOOKUP(E2,A2:D6,4,FALSE)="","",VLOOKUP(E2,A2:D6,4,FALSE))

See how to make VLOOKUP return a blank instead of 0 for more ways to do this.

Example 5: use IF to choose what VLOOKUP returns#

You can also turn the formula around and put IF inside VLOOKUP. IF then picks the column number. Say F1 holds either Stock or Item:

=VLOOKUP(E2,A2:D6,IF(F1="Stock",3,2),FALSE)

When F1 says Stock, IF returns 3 and VLOOKUP brings back the stock count. Otherwise it returns 2, the item name. The same idea works for two whole lookups, for example a member price list and a standard price list:

=IF(F2="Member",VLOOKUP(E2,H2:I6,2,FALSE),VLOOKUP(E2,K2:L6,2,FALSE))

Only one VLOOKUP runs, depending on F2.

Common mistakes with IF and VLOOKUP#

These cause most wrong results:

1. Leaving out FALSE. Without it, VLOOKUP does an approximate match and can return a value from the wrong row on an unsorted list. Always end with FALSE.
2. Numbers in quotes. <"20" compares with text. Write <20.
3. Numbers stored as text. If the codes are numbers in one table and text in the other, VLOOKUP returns #N/A. Make both the same type.
4. Brackets in the wrong place. VLOOKUP's closing bracket goes before the comparison: VLOOKUP(...)<20, not VLOOKUP(...<20).
5. Hard-coded column numbers. If someone inserts a column inside the table, column 3 is no longer Stock. Check the formula after changing the table, or use XLOOKUP (Excel 2021 or Microsoft 365), which points at the column itself.

The IF function guide and the VLOOKUP function guide list every argument if you get stuck.

Practise it#

You learn this pattern by typing it a few times. Practise the IF function in a live spreadsheet: you write each test and the sheet checks it instantly. Then practise VLOOKUP exact match until the four arguments come without thinking, and try the exercise on this page to put the two together.