XLOOKUP function in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a quick way to find product prices by entering the product name.

📊 Data: You have a list of products with their IDs, names, and prices.

🎯 Deliverable: Create a lookup tool where entering a product name returns its price using the XLOOKUP function.

Sample Data

Product ID	Product Name	Price
101	Notebook	2.50
102	Pen	1.20
103	Eraser	0.80
104	Marker	1.50
105	Stapler	5.00
106	Folder	3.00
107	Glue	1.75
108	Scissors	4.25

1

Step 1: Create a cell where you will type the product name to search. For example, use cell E2 and label it 'Enter Product Name'.

No formula needed, just type the product name here later.

Expected Result

Cell E2 is empty and ready for input.

2

Step 2: In the cell next to it (F2), write the XLOOKUP formula to find the price based on the product name typed in E2.

=XLOOKUP(E2, B2:B9, C2:C9, "Not Found")

Expected Result

If you type 'Pen' in E2, F2 shows 1.20; if product not found, shows 'Not Found'.

3

Step 3: Test the lookup by typing different product names in E2 and check if the correct price appears in F2.

Type 'Marker', 'Stapler', or 'Glue' in E2 and watch F2 update.

Expected Result

F2 updates to 1.50, 5.00, or 1.75 respectively.

4

Step 4: Format the price cell (F2) to show two decimal places for clarity.

Select F2, then Format > Number > Number with 2 decimal places.

Expected Result

Prices like 1.2 show as 1.20 for consistency.

Final Result

Enter Product Name | Price
-------------------|-------
Pen                | 1.20

Type a product name in the left cell, and the price appears on the right.

✓XLOOKUP can quickly find prices by product name.

✓It shows a friendly message if the product is not in the list.

✓This tool saves time compared to searching manually.

Bonus Challenge

Modify the formula to also return 'Check spelling' if the product name is close but not exact.

Show Hint

Use the optional match_mode argument in XLOOKUP with approximate match.