VLOOKUP function in Excel - Real Business Scenario

Scenario Mode

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

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

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

🎯 Deliverable: Create a table where entering a product code shows the product name and price using VLOOKUP.

Sample Data

Product Code	Product Name	Price
P001	Notebook	2.50
P002	Pen	1.20
P003	Stapler	5.00
P004	Marker	1.80
P005	Folder	3.00
P006	Paper Clips	0.90
P007	Highlighter	1.50
P008	Envelope	0.70

1

Step 1: Create a new table with a cell to enter the product code, and two cells to show product name and price.

Set cell B2 for product code input, C2 for product name output, D2 for price output.

Expected Result

You have cells ready: B2 (input), C2 and D2 (outputs).

2

Step 2: Use VLOOKUP in cell C2 to find the product name based on the product code entered in B2.

=VLOOKUP(B2, A5:C12, 2, FALSE)

Expected Result

When you enter 'P003' in B2, C2 shows 'Stapler'.

3

Step 3: Use VLOOKUP in cell D2 to find the product price based on the product code entered in B2.

=VLOOKUP(B2, A5:C12, 3, FALSE)

Expected Result

When you enter 'P003' in B2, D2 shows 5.00.

4

Step 4: Test by entering different product codes in B2 to see if the name and price update correctly.

Enter 'P001', 'P005', or 'P008' in B2 and watch C2 and D2 update.

Expected Result

For 'P001', C2='Notebook', D2=2.50; for 'P005', C2='Folder', D2=3.00; for 'P008', C2='Envelope', D2=0.70.

Final Result

Product Lookup Table
+--------------+--------------+-------+
| Product Code | Product Name | Price |
+--------------+--------------+-------+
|     P003     |   Stapler    |  5.00 |
+--------------+--------------+-------+

Enter product code in B2 to see name and price.

✓VLOOKUP helps find matching data in a table by looking up a key value.

✓Using FALSE as the last argument ensures exact matches only.

✓This method quickly shows product details by code without searching manually.

Bonus Challenge

Modify the VLOOKUP formulas to show 'Not Found' if the product code does not exist.

Show Hint

Use the IFERROR function around VLOOKUP, like =IFERROR(VLOOKUP(...), "Not Found")