Level 5 · Product names with XLOOKUP

VLOOKUP has two habits you met in level 3: it only searches the first column of its table, and it counts columns by number. Thomas’s catalog export breaks the first rule. The id is in the last column, I, with the name and subcategory to its left in G and H. VLOOKUP can’t look left. XLOOKUP, the modern lookup in Excel 2021, Microsoft 365 and Google Sheets, can.

Search one range, return another

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Argument	In D2	Means
lookup_value	B2	the id to find (108)
lookup_array	$I$2:$I$17	the column to search
return_array	$G$2:$G$17	the column to bring back from
if_not_found	"discontinued"	what to show when the id isn’t there

Instead of a table and a column number, XLOOKUP takes two separate ranges. For row 2 it finds 108 on the 7th row of I2:I17 and returns the 7th value of G2:G17: Ashford Leather Dining Chair. For the subcategory, change only the return range to $H$2:$H$17, which gives Chairs. Which side the return range is on doesn’t matter, so a right-to-left lookup is no harder than a left-to-right one. The two ranges must be the same height; if they aren’t, XLOOKUP shows #VALUE!.

Exact match by default

XLOOKUP looks for exactly 108 unless you ask for something else, and it doesn’t care how the catalog is sorted. No FALSE to remember. Its fifth argument can ask for the next smaller or larger value instead, which suits band tables like the shipping fees in level 1, but for ids you want the default.

A fallback built in

The catalog only lists products on sale today. Product 106 was discontinued, and it appears on three lines (rows 11, 19 and 20). Without a fourth argument, XLOOKUP(B11,…) shows #N/A. With "discontinued", the cell says what happened, with no IFERROR wrapped around it.

Use a fallback only when you know why keys are missing. Here the reason is clear. If dozens of ids were missing for no reason, the honest move would be to leave the #N/A and find out why.

Lock the lookup ranges

Every row searches the same catalog, so both ranges need $. Filled down without them, I2:I17 becomes I3:I18 on the next row, then I4:I19, and the catalog slides away one row at a time. On this sheet that turns the line in row 8 (product 105, Avery Leather Bed Frame) into “discontinued”, because 105 has already slid out of the search. In total, eight lines of products still on sale end up marked discontinued.

Common mistakes

• Swapping the two ranges. XLOOKUP(B2,$G$2:$G$17,$I$2:$I$17) searches the names for 108 and finds nothing.
• Unlocked ranges. The first rows look right, lower rows report products as missing.
• A number stored as text. If the ids in one table are text ("108"), XLOOKUP won’t match them to the number 108. Text sits on the left of the cell, numbers on the right.
• Hiding a real problem. A fallback on every lookup can cover up a broken join. Count the fallbacks: here it is exactly the 3 lines of product 106.
• Expecting a spill. In Microsoft 365 and Google Sheets, XLOOKUP(B2,$I$2:$I$17,$G$2:$H$17) returns name and subcategory at once and spills into E. This practice sheet keeps one value per cell, so write one formula per column.