XLOOKUP function in Excel - 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 and stock quantities by entering the product name.

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

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

Sample Data

Product ID	Product Name	Price	Stock Quantity
101	Notebook	2.50	150
102	Pen	1.20	500
103	Eraser	0.80	300
104	Marker	1.50	200
105	Stapler	5.00	75
106	Folder	3.00	120
107	Glue	1.75	180
108	Scissors	4.00	90

1

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

Expected Result

Cell G2 is empty and ready for input.

2

Step 2: In cell H2, write a formula to find the price of the product typed in G2 using XLOOKUP.

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

Expected Result

If you type 'Pen' in G2, H2 shows 1.20.

3

Step 3: In cell I2, write a formula to find the stock quantity of the product typed in G2 using XLOOKUP.

=XLOOKUP(G2, B2:B9, D2:D9, "Not Found")

Expected Result

If you type 'Pen' in G2, I2 shows 500.

4

Step 4: Test the lookup by typing different product names in G2 and check that H2 and I2 update correctly.

Expected Result

Typing 'Scissors' in G2 shows 4.00 in H2 and 90 in I2.

Final Result

Enter Product Name: [Pen]
Price: 1.20
Stock Quantity: 500

✓XLOOKUP can quickly find matching data in a list based on a search term.

✓It returns a friendly message like 'Not Found' if the product name is not in the list.

✓This tool helps the manager get product info fast without searching manually.

Bonus Challenge

Modify the lookup to be case-insensitive so that typing 'pen' or 'PEN' still finds the product.

Show Hint

Use the LOWER function on both the lookup value and lookup array inside XLOOKUP.