CURRENT EXCEL
Use XLOOKUP's not-found argument
Here A2 is the key, column A on Sheet2 contains keys, and column C contains the result. The fourth argument is returned only when Excel cannot find the key.
Blank only when the lookup key is not found
=XLOOKUP(A2,Sheet2!A:A,Sheet2!C:C,"")VLOOKUP
Use IFNA around VLOOKUP in older workbooks
Exact-match VLOOKUP with a blank for #N/A
=IFNA(VLOOKUP(A2,Sheet2!A:C,3,FALSE),"")IFNA catches the lookup miss without hiding unrelated errors such as a broken reference. FALSE requests an exact match; omitting it can produce a plausible but incorrect result.
BOUNDARY
Do not collapse three meanings into one
- • Key missing: no matching row exists; choose a blank or a visible “Not found” label.
- • Source blank: a matching row exists but its result cell is empty; preserve that distinction.
- • Source zero: a matching row exists and the value is 0; do not convert the zero to blank by accident.
SOURCE