VLOOKUP function in Google Sheets - Real Business Scenario

Scenario Mode

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

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

📊 Data: You have a product list with columns: Product ID, Product Name, and Price.

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

Sample Data

Product ID	Product Name	Price
P001	Notebook	2.50
P002	Pen	1.20
P003	Stapler	5.00
P004	Marker	1.75
P005	Folder	3.00
P006	Glue	1.00
P007	Scissors	4.50
P008	Ruler	1.30

1

Step 1: Create a new sheet or area for the lookup table. In cell A2, type a product ID to search, for example, 'P003'.

Expected Result

Cell A2 contains 'P003'.

2

Step 2: In cell B2, use VLOOKUP to find the product name for the ID in A2. Enter the formula: =VLOOKUP(A2, A5:C12, 2, FALSE)

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

Expected Result

Cell B2 shows 'Stapler'.

3

Step 3: In cell C2, use VLOOKUP to find the price for the product ID in A2. Enter the formula: =VLOOKUP(A2, A5:C12, 3, FALSE)

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

Expected Result

Cell C2 shows 5.00.

4

Step 4: Test by changing the product ID in A2 to 'P007'. The product name and price should update automatically.

Expected Result

B2 shows 'Scissors' and C2 shows 4.50.

5

Step 5: Format the price column (C2) as currency for better readability.

Select C2, Format > Number > Currency

Expected Result

Price in C2 displays as $5.00 or local currency format.

Final Result

Product Lookup Table
+------------+--------------+-------+
| Product ID | Product Name | Price |
+------------+--------------+-------+
| P003       | Stapler      | $5.00 |
+------------+--------------+-------+

✓VLOOKUP helps find product details quickly by product ID.

✓Changing the product ID updates the product name and price automatically.

✓Using FALSE in VLOOKUP ensures exact match lookup.

Bonus Challenge

Modify the lookup to show 'Not Found' if the product ID does not exist in the list.

Show Hint

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