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

Q: How do I keep a line total blank too?

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

Q: 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.

Formula (Excel and Google Sheets)

=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))

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.

Result on the example: one result per row in B2, filled down to B7
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

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.

=IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))

Result59

A2"K-301"
The value in A2.
A2=""FALSE
The = operator tests whether the two sides are equal.
$E$2:$E$76 rows × 1 col: "K-301", "K-305", "P-210", "P-214", …
A range: 6 cells in one column.
$F$2:$F$76 rows × 1 col: $59, $19, $49, $69, …
A range: 6 cells in one column.
XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU")59
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueA2"K-301"
- lookup_array$E$2:$E$76 rows × 1 col: "K-301", "K-305", "P-210", "P-214", …
- return_array$F$2:$F$76 rows × 1 col: $59, $19, $49, $69, …
- if_not_found"Unknown SKU"
IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU"))59
IF: Returns one value if a test is true, another if false.
- logical_testA2=""FALSE
- value_if_true""not used
- value_if_falseXLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU")59

In Google Sheets

The same formula works in Google Sheets: =IF(A2="","",XLOOKUP(A2,$E$2:$E$7,$F$2:$F$7,"Unknown SKU")). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=IF(A2="","",IFNA(VLOOKUP(A2,$E$2:$F$7,2,FALSE),"Unknown SKU"))	$59, $24, …	The same logic for Excel 2019 and earlier.
=IFERROR(VLOOKUP(A2,$E$2:$F$7,2,FALSE),"")	$59, $24, …Different result	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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Keep a lookup blank until a value is entered

This is an order form. In B2, return the price for the SKU in column A, show nothing while the SKU is empty, and "Unknown SKU" when it isn't in the list. Fill down to B7.

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.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.