Excel XLOOKUP Missing SKU Fallback: Return “SKU Not in List” Instead of #N/A

To replace #N/A for a missing SKU, enter =XLOOKUP(D1,A1:A3,B1:B3,"SKU Not in List") in E1. The formula searches the SKU typed in D1 and returns the matching inventory status from column B; when the SKU is absent, it displays SKU Not in List.

XLOOKUP accepts an optional if_not_found argument. Its default match mode is exact match, and it returns the first match it finds. These behaviors are documented in Microsoft’s XLOOKUP function reference.

CONTENTS

Set up the missing-SKU fallback

In this synthetic inventory list, A1:A3 contains SKU-ALPHA, SKU-BRAVO, and SKU-CHARLIE; B1:B3 contains In Stock, Reorder, and Discontinued; and D1 contains SKU-DELTA.

Cell	Purpose	Value
A1	Inventory SKU	SKU-ALPHA
A2	Inventory SKU	SKU-BRAVO
A3	Inventory SKU	SKU-CHARLIE
B1	Inventory status	In Stock
B2	Inventory status	Reorder
B3	Inventory status	Discontinued
D1	Order SKU to check	SKU-DELTA

Enter the inventory SKU values in A1:A3 and their corresponding statuses in B1:B3.
Enter the order SKU to check in D1.
Use =XLOOKUP(D1,A1:A3,B1:B3,"SKU Not in List") in E1, where D1 is the order SKU, A1:A3 is the inventory SKU list, B1:B3 is the status to return, and SKU Not in List is the literal fallback.
When D1 contains SKU-DELTA, which is not in A1:A3, E1 displays SKU Not in List instead of #N/A.

Understand the formula

D1 is the lookup value: the SKU from the order. A1:A3 is the lookup array: the inventory SKUs to search. B1:B3 is the return array: the status from the same row as a matching SKU.

The final argument, "SKU Not in List", supplies literal text for a lookup that has no valid match. If it is omitted, an exact lookup with no match returns #N/A.

If D1 contains SKU-BRAVO, E1 returns Reorder because an exact matching SKU exists.

Separate missing and duplicate SKUs

A missing SKU is a value that does not occur in A1:A3. That is when the fallback text is returned. A duplicate SKU is different: a match exists, so XLOOKUP returns the result beside the first occurrence.

Cell	Value
A1	SKU-ALPHA
A2	SKU-BRAVO
A3	SKU-BRAVO
B1	In Stock
B2	Reorder
B3	Hold
D1	SKU-BRAVO

With the unchanged formula =XLOOKUP(D1,A1:A3,B1:B3,"SKU Not in List"), E1 returns Reorder, the status beside the first SKU-BRAVO. If SKU-BRAVO appears twice in A1:A3, XLOOKUP returns the status from its first occurrence; the fallback is not used because the SKU was found.

This formula returns only the first matching SKU, so it does not identify duplicate SKU records or return every matching row. Resolve repeated keys before relying on a returned status; see the guidance for troubleshooting XLOOKUP duplicate keys.

Resolve common symptoms

Symptom	Likely cause	Concrete fix
E1 shows SKU Not in List for a SKU that looks correct.	The typed value differs from the inventory value, such as an extra space or a different SKU character.	Compare D1 with the intended SKU in column A and standardize SKU entry.
E1 returns a status, but it is not the intended record.	The SKU occurs more than once in A1:A3.	Find and resolve repeated SKU values; XLOOKUP returns the first match rather than a duplicate warning.
E1 returns #N/A rather than the message.	The fourth argument was omitted or the fallback text was not entered as quoted text.	Use the complete formula: `=XLOOKUP(D1,A1:A3,B1:B3,"SKU Not in List")`.

Compatibility and fallback

This formula is available in Excel for Microsoft 365, Excel 2021, and Excel 2024 on Windows and Mac; Microsoft documents XLOOKUP as unavailable in Excel 2016 and Excel 2019.

If XLOOKUP is unavailable, use a version of Excel that supports XLOOKUP or use an older-version lookup approach appropriate to the workbook’s layout. XLOOKUP uses separate lookup and return arrays, so the return column can be on either side of the lookup column. For an introduction to those arguments, see Excel XLOOKUP basics.

Worked example details and boundary cases

Expected result

When D1 contains SKU-DELTA, which is not in A1:A3, E1 displays SKU Not in List instead of #N/A.

Boundary cases

If D1 contains SKU-BRAVO, E1 returns Reorder because an exact matching SKU exists.

If SKU-BRAVO appears twice in A1:A3, XLOOKUP returns the status from its first occurrence; the fallback is not used because the SKU was found.

If a user types SKU-DELTA with an extra space or different SKU text, it is a different lookup value and may return SKU Not in List; standardize SKU entry before relying on the message.

Practice with the exact example data

Use a separate blank worksheet for each example. Enter each value at its listed cell address, then type the formula in AA1. Quoted text is shown in JSON notation to preserve spaces: enter the characters inside the quotes, without the quotes, using a leading apostrophe to keep text and spaces literal. An empty quoted string means an explicit empty text value; use an apostrophe entry where appropriate. Compare your result with the expected result below. These are synthetic practice data.

Example 1 — Main case

Cell	Input value
A1	“SKU-ALPHA”
A2	“SKU-BRAVO”
A3	“SKU-CHARLIE”
B1	“In Stock”
B2	“Reorder”
B3	“Discontinued”
D1	“SKU-BRAVO”

Formula in AA1: =XLOOKUP(D1,A1:A3,B1:B3,"SKU Not in List")

Expected result: “Reorder”

Example 2 — Boundary case

Cell	Input value
A1	“SKU-ALPHA”
A2	“SKU-BRAVO”
A3	“SKU-BRAVO”
B1	“In Stock”
B2	“Reorder”
B3	“Hold”
D1	“SKU-BRAVO”