# How to use IF with VLOOKUP in Excel

Use the VLOOKUP result inside the IF test: =IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder").

**Excel:** `=IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Order | SKU | Qty | Availability |  | SKU | In stock |
| SO-1 | P-101 | 5 |  |  | P-101 | 140 |
| SO-2 | P-104 | 25 |  |  | P-102 | 32 |
| SO-3 | P-102 | 12 |  |  | P-103 | 0 |
| SO-4 | P-103 | 1 |  |  | P-104 | 18 |
| SO-5 | P-104 | 18 |  |  |  |  |

Formula in D2, filled to D6: `=IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder")`

Result: Ship; Backorder; Ship; Backorder; Ship

## How it works

VLOOKUP returns the stock for the SKU, and that number is compared with the quantity like any other value. IF turns the comparison into a decision. SO-5 asks for exactly the 18 in stock, so it ships.

## Other ways

- `=IF(XLOOKUP(B2,$F$2:$F$5,$G$2:$G$5)>=C2,"Ship","Backorder")`: The XLOOKUP version, with separate lookup and return columns.
- `=IFNA(IF(VLOOKUP(B2,$F$2:$G$5,2,FALSE)>=C2,"Ship","Backorder"),"Unknown SKU")`: Shows a message for SKUs missing from the stock table instead of #N/A.

## Common mistakes

- Without FALSE, VLOOKUP does an approximate match and can compare against another product's stock.
- A SKU missing from the table makes the whole formula return #N/A. Wrap it in IFNA.
- Lock the stock table with $ before filling down.
- Stock numbers stored as text compare wrongly: Excel ranks any text above any number, so "4" >= 25 is TRUE and the order shows "Ship". Convert the stock column to real numbers first.

## Questions

**Can IF choose which table VLOOKUP searches?**

Yes: `=VLOOKUP(B2,IF(A2="East",$F$2:$G$5,$I$2:$J$5),2,FALSE)`.

**How do I return a blank when the SKU isn't found?**

`=IFNA(VLOOKUP(B2,$F$2:$G$5,2,FALSE),"")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-with-vlookup