Bird
Raised Fist0
Excelspreadsheet~15 mins

VLOOKUP function in Excel - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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.
Progress0 / 4 steps
Sample Data
Product CodeProduct NamePrice
P001Notebook2.50
P002Pen1.20
P003Stapler5.00
P004Marker1.80
P005Folder3.00
P006Paper Clips0.90
P007Highlighter1.50
P008Envelope0.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")

Practice

(1/5)
1. What does the VLOOKUP function do in Excel?
easy
A. It sorts data alphabetically.
B. It adds up all numbers in a column.
C. It creates a chart from selected data.
D. It looks for a value in the first column of a table and returns a value from the same row.

Solution

  1. Step 1: Understand VLOOKUP purpose

    VLOOKUP searches for a value in the first column of a table.

  2. Step 2: Identify what it returns

    It returns a value from the same row but a different column you specify.

  3. Final Answer:

    It looks for a value in the first column of a table and returns a value from the same row. -> Option D

  4. Quick Check:

    VLOOKUP finds and returns matching row data [OK]
Hint: VLOOKUP always searches first column, returns from chosen column [OK]
Common Mistakes:
  • Thinking VLOOKUP sums or sorts data
  • Confusing VLOOKUP with chart tools
  • Assuming it searches any column, not just first
2. Which of these is the correct syntax for an exact match VLOOKUP formula to find value in A2 in table B2:D10 and return column 3?
easy
A. =VLOOKUP(A2, B2:D10, 2, FALSE)
B. =VLOOKUP(A2, B2:D10, 3, TRUE)
C. =VLOOKUP(A2, B2:D10, 3, FALSE)
D. =VLOOKUP(A2, B2:D10, 3)

Solution

  1. Step 1: Identify exact match argument

    For exact match, the fourth argument must be FALSE.

  2. Step 2: Check column index and range

    We want column 3 from range B2:D10, so column index is 3.

  3. Final Answer:

    =VLOOKUP(A2, B2:D10, 3, FALSE) -> Option C

  4. Quick Check:

    Exact match needs FALSE as last argument [OK]
Hint: Use FALSE for exact match, TRUE for approximate [OK]
Common Mistakes:
  • Using TRUE instead of FALSE for exact match
  • Forgetting the column number or using wrong one
  • Omitting the last argument defaults to TRUE (approximate)
3. Given this table in cells A1:B4:

Name | Score
John | 85
Mary | 92
Alex | 78

What is the result of =VLOOKUP("Mary", A2:B4, 2, FALSE)?
medium
A. 85
B. 92
C. Mary
D. #N/A

Solution

  1. Step 1: Locate lookup value in first column

    "Mary" is found in cell A3.

  2. Step 2: Return value from column 2 same row

    Column 2 in row 3 is 92.

  3. Final Answer:

    92 -> Option B

  4. Quick Check:

    VLOOKUP returns matching row's column 2 value [OK]
Hint: VLOOKUP returns value from specified column in matched row [OK]
Common Mistakes:
  • Returning value from wrong column index
  • Getting #N/A if exact match not found
  • Confusing lookup value with return value
4. You wrote =VLOOKUP(B2, A2:C10, 4, FALSE) but get an error. What is the problem?
medium
A. The column index 4 is outside the table range columns.
B. The lookup value B2 is missing.
C. The range A2:C10 is not sorted.
D. The FALSE argument is invalid.

Solution

  1. Step 1: Check table range columns

    Range A2:C10 has 3 columns only.

  2. Step 2: Verify column index argument

    Column index 4 is invalid because it exceeds the number of columns in the range.

  3. Final Answer:

    The column index 4 is outside the table range columns. -> Option A

  4. Quick Check:

    Column index must be ≤ number of columns in range [OK]
Hint: Column index can't be bigger than table columns count [OK]
Common Mistakes:
  • Using column index larger than table columns
  • Assuming FALSE is invalid argument
  • Thinking lookup value must be in range
5. You have a product list in A2:C6:

Code | Name | Price
P01 | Pen | 1.5
P02 | Book | 7.0
P03 | Bag | 12.5
P04 | Cup | 3.0
P05 | Box | 4.5

How would you write a formula to find the price of product code in E2 exactly, returning "Not found" if code is missing?
hard
A. =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found")
B. =VLOOKUP(E2, A2:C6, 3, TRUE)
C. =VLOOKUP(E2, A2:C6, 2, FALSE)
D. =IF(VLOOKUP(E2, A2:C6, 3, FALSE) = 0, "Not found", VLOOKUP(E2, A2:C6, 3, FALSE))

Solution

  1. Step 1: Use VLOOKUP for exact match price

    VLOOKUP with FALSE finds exact product code and returns price from column 3.

  2. Step 2: Handle missing code with IFERROR

    Wrap VLOOKUP in IFERROR to return "Not found" if code is missing.

  3. Final Answer:

    =IFERROR(VLOOKUP(E2, A2:C6, 3, FALSE), "Not found") -> Option A

  4. Quick Check:

    IFERROR catches errors, shows custom message [OK]
Hint: Wrap VLOOKUP in IFERROR to show message if no match [OK]
Common Mistakes:
  • Using TRUE instead of FALSE for exact match
  • Returning wrong column index value
  • Not handling missing lookup values causing errors
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard