You receive an order file with only Product Codes. Your manager needs Product Name, Category, Supplier and Unit Price added to every order. Instead of manually searching the master file, XLOOKUP can fetch all details automatically.

What does XLOOKUP do?

XLOOKUP searches for a value in one column and returns related information from another column. It is useful for product lookup, employee lookup, customer lookup, price lookup and master-data matching.

Simple rule: Use XLOOKUP when you have an ID/code and need to return matching details from a master table.

XLOOKUP formula structure

Basic syntax

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found)

Example: =XLOOKUP(B4,'Product Master'!A:A,'Product Master'!B:B,"Not Found")

This looks for the product code in B4, searches the Product Code column, and returns the matching Product Name.

Business examples

NeedFormulaResult
Find product name=XLOOKUP(B4,Master!A:A,Master!B:B,"Not Found")Laptop Stand
Find unit price=XLOOKUP(B4,Master!A:A,Master!E:E,0)₹450
Wrong product code=XLOOKUP(B4,Master!A:A,Master!B:B,"Not Found")Not Found
IDLookup value
CodeLookup array
NameReturn array

Why XLOOKUP is powerful

Cleaner than VLOOKUPYou directly select lookup and return arrays instead of counting column numbers.
Can look leftThe return column can be left or right of the lookup column.
Built-in not found handlingYou can show “Not Found” instead of confusing errors.
Exact match by defaultGood for product codes, employee IDs and customer IDs.
💡
Professional habit: Always add an if_not_found message so wrong codes are easy to identify.
⚠️
Common mistake: Looking up codes with extra spaces or inconsistent spelling. Clean the code column before blaming the formula.
🧪 Practice Lab

Enrich orders using product master data

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, formula cheat sheet, quiz and answer key.

📦 Download Lesson 18 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Use XLOOKUP to fetch Product Name from Product Code.
  2. Use XLOOKUP to fetch Category and Supplier.
  3. Use XLOOKUP to fetch Unit Price.
  4. Calculate Total Sales.
  5. Handle invalid product codes using “Not Found”.

Quick quiz

  1. What is XLOOKUP used for?
  2. What are the three required parts?
  3. What does if_not_found do?
  4. Can XLOOKUP return values from the left?

Answers: Lookup and return related data; lookup value, lookup array, return array; shows a custom result when no match is found; yes.

FAQ

For most modern Excel users, yes. XLOOKUP is easier to read, can look left, and has built-in not-found handling.
Usually the lookup value does not exist, has extra spaces, different spelling, or different formatting.