XLOOKUP with exact match
BeginnerWhen managing inventory, you often have two separate lists: a master inventory table and a shorter reorder list. XLOOKUP makes it easy to pull values from one list into the other.
XLOOKUP searches for a value in a lookup column and returns a value from a return column in the same row. The three required arguments are:
=XLOOKUP(lookup_value, lookup_array, return_array)
- lookup_value: the value you want to find (a SKU, a name, an ID, etc.)
- lookup_array: the column to search in
- return_array: the column to return a value from
XLOOKUP uses exact match by default, so no extra argument is needed for that.
Your task
A warehouse inventory table is in rows 1–8 (SKU, Item name, Qty in stock). Below it, rows 10–14 show a reorder list with the same columns. The SKU and Item name columns are already filled in.
- Click cell C11.
- Write an XLOOKUP formula that finds the SKU from A11 in the inventory SKU column ($A$2:$A$8).
- Return the matching value from the Qty in stock column ($C$2:$C$8).
- Copy the formula down through C14.
When you're done, cells C11:C14 should show the current stock quantity for each item on the reorder list.
Need some help?
Hint 1
If XLOOKUP returns #N/A, it means no match was found. Check that the SKU in your reorder list exactly matches the SKU in the inventory table, including capitalisation and any spaces.
Hint 2
Unlike VLOOKUP, XLOOKUP does not use a column index number. You point directly to the return column ($C$2:$C$8), and XLOOKUP returns the value from the same row as the match.
XLOOKUP with exact match
BeginnerWhen managing inventory, you often have two separate lists: a master inventory table and a shorter reorder list. XLOOKUP makes it easy to pull values from one list into the other.
XLOOKUP searches for a value in a lookup column and returns a value from a return column in the same row. The three required arguments are:
=XLOOKUP(lookup_value, lookup_array, return_array)
- lookup_value: the value you want to find (a SKU, a name, an ID, etc.)
- lookup_array: the column to search in
- return_array: the column to return a value from
XLOOKUP uses exact match by default, so no extra argument is needed for that.
Your task
A warehouse inventory table is in rows 1–8 (SKU, Item name, Qty in stock). Below it, rows 10–14 show a reorder list with the same columns. The SKU and Item name columns are already filled in.
- Click cell C11.
- Write an XLOOKUP formula that finds the SKU from A11 in the inventory SKU column ($A$2:$A$8).
- Return the matching value from the Qty in stock column ($C$2:$C$8).
- Copy the formula down through C14.
When you're done, cells C11:C14 should show the current stock quantity for each item on the reorder list.
Need some help?
Hint 1
If XLOOKUP returns #N/A, it means no match was found. Check that the SKU in your reorder list exactly matches the SKU in the inventory table, including capitalisation and any spaces.
Hint 2
Unlike VLOOKUP, XLOOKUP does not use a column index number. You point directly to the return column ($C$2:$C$8), and XLOOKUP returns the value from the same row as the match.