VLOOKUP function in Excel - Dashboard Guide

Dashboard Mode - VLOOKUP function

Goal

Find product prices quickly by looking up product names in a list.

Sample Data

Product	Price
Apple	1.20
Banana	0.50
Carrot	0.30
Date	1.50
Eggplant	0.80

Dashboard Components

• Input Cell: Cell E2 where user types a product name (e.g., "Banana")
• Price Lookup Result: Cell E3 shows the price using formula =VLOOKUP(E2, A2:B6, 2, FALSE). This finds the exact price for the product typed in E2.
• Product List Table: Shows all products and prices from A2:B6 for reference.

Dashboard Layout

+-------------------+-------------------+
| Product List      | Lookup Input &    |
| (A2:B6)           | Price Result      |
|                   |                   |
| Apple     1.20    | Product: [ E2 ]   |
| Banana    0.50    | Price:   [ E3 ]   |
| Carrot    0.30    |                   |
| Date      1.50    |                   |
| Eggplant  0.80    |                   |
+-------------------+-------------------+

Interactivity

User types a product name in cell E2. The formula in E3 updates automatically to show the matching price from the product list. If the product is not found, the formula returns an error.

Self Check

Type "Carrot" in cell E2. What price appears in E3? (Expected: 0.30)

Key Result

Dashboard to look up product prices by typing product names using VLOOKUP.

Practice

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