# How to keep a lookup blank until a value is entered in Excel

Test the input first: `=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))` stays empty on empty rows and still flags typos.

**Excel:** `=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| SKU | Unit price |  |  | SKU | Unit price |
| K-301 |  |  |  | K-301 | 59 |
| B-102 |  |  |  | K-305 | 19 |
|  |  |  |  | P-210 | 49 |
| P-214 |  |  |  | P-214 | 69 |
|  |  |  |  | B-102 | 24 |
| K-39 |  |  |  | B-108 | 89 |

Formula in B2, filled to B7: `=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))`

Result: $59; $24; ; $69; ; Unknown SKU

## How it works

IF checks the input cell before looking anything up. An empty SKU returns "", so the form shows blank lines instead of #N/A. A typed SKU goes to XLOOKUP, and a typo like K-39 still shows "Unknown SKU", so mistakes don't disappear.

## Other ways

- `=IF(A2="","",IFNA(VLOOKUP(A2,$E$2:$F$7,2,FALSE),"Unknown SKU"))`: The same logic for Excel 2019 and earlier.
- `=IFERROR(VLOOKUP(A2,$E$2:$F$7,2,FALSE),"")`: Shorter, but it blanks out typos as well as empty rows. (The mistyped K-39 shows blank instead of "Unknown SKU".)

## Common mistakes

- IFERROR around the lookup hides empty rows and real mistakes alike. Test for the empty input instead.
- An input with a single space isn't "". Use `TRIM(A2)=""` if that happens.

## Questions

**How do I keep a line total blank too?**

Wrap it the same way: `=IF(A2="","",B2*C2)`.

**How do I do this with one formula for the whole column?**

In Excel 2021 or Microsoft 365, `=IF(A2:A7="","",XLOOKUP(A2:A7,E2:E7,F2:F7,"Unknown SKU"))` spills down.

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/blank-lookup-until-input