VLOOKUP function in Google Sheets - 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 D2 where user types a product name (e.g., "Banana")
• Price Result Cell: Cell D3 shows the price using formula =VLOOKUP(D2, A2:B6, 2, FALSE)
• Product List Table: Shows all products and prices (A2:B6)

Dashboard Layout

+----------------------+------------------+
| Product List         | Lookup Price     |
| (A2:B6)              |                  |
|                      | Product Name:    |
| Apple     1.20       | [ D2 input cell ]|
| Banana    0.50       | Price:           |
| Carrot    0.30       | [ D3 result cell ]|
| Date      1.50       |                  |
| Eggplant  0.80       |                  |
+----------------------+------------------+

Interactivity

User types a product name in cell D2. The formula in D3 uses VLOOKUP to find the price from the product list. If the product exists, the price shows; if not, an error appears.

Self Check

Type "Carrot" in D2. What price appears in D3? (Expected: 0.30)

Key Result

Lookup product prices by entering product names using VLOOKUP in Google Sheets.

Practice

1.

What does the VLOOKUP function do in Google Sheets?

easy

A. It looks for a value in the first column of a range and returns a value from another column in the same row.

B. It adds all numbers in a column.

C. It sorts data alphabetically.

D. It counts the number of cells with numbers.

Solution

1. Step 1: Understand the purpose of VLOOKUP

   VLOOKUP searches for a value in the first column of a given range.

2. Step 2: Identify what it returns

   It returns a value from another column in the same row where the match is found.

3. Final Answer:

   It looks for a value in the first column of a range and returns a value from another column in the same row. -> Option A

4. Quick Check:

   VLOOKUP finds and returns matching row data [OK]

Hint: Remember: VLOOKUP searches first column, returns from another [OK]

Common Mistakes:

• Thinking VLOOKUP sums or counts data
• Confusing VLOOKUP with sorting functions
• Assuming it searches all columns, not just the first

2.

Which of the following is the correct syntax for an exact match VLOOKUP in Google Sheets?

=VLOOKUP(lookup_value, range, col_index, FALSE)

easy

A. =VLOOKUP(range, lookup_value, col_index, FALSE)

B. =VLOOKUP(lookup_value, range, col_index, TRUE)

C. =VLOOKUP(lookup_value, range, col_index, FALSE)

D. =VLOOKUP(lookup_value, col_index, range, FALSE)

Solution

1. Step 1: Recall VLOOKUP syntax order

   The correct order is lookup_value, range, column index, and match type.

2. Step 2: Identify exact match parameter

   FALSE is used for exact match, TRUE for approximate.

3. Final Answer:

   =VLOOKUP(lookup_value, range, col_index, FALSE) -> Option C

4. Quick Check:

   Exact match uses FALSE as last argument [OK]

Hint: Exact match always ends with FALSE in VLOOKUP [OK]

Common Mistakes:

• Swapping lookup_value and range
• Using TRUE instead of FALSE for exact match
• Mixing order of parameters

3.

Given this table in cells A1:B4:

A1: Product  B1: Price
A2: Apple    B2: 1.5
A3: Banana   B3: 0.75
A4: Cherry   B4: 2.0

What will =VLOOKUP("Banana", A1:B4, 2, FALSE) return?

medium

A. Banana

B. 0.75

C. 1.5

D. 2.0

Solution

1. Step 1: Locate "Banana" in first column

   Banana is found in cell A3.

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

   The second column in row 3 is B3, which is 0.75.

3. Final Answer:

   0.75 -> Option B

4. Quick Check:

   VLOOKUP returns price 0.75 for Banana [OK]

Hint: Find lookup in first column, return from given column index [OK]

Common Mistakes:

• Returning the lookup value instead of the result column
• Using approximate match causing wrong results
• Confusing column index numbers

4.

What is wrong with this formula if you want an exact match lookup?

=VLOOKUP("Orange", A2:B5, 2)

medium

A. Using quotes around Orange

B. Missing the column index parameter

C. Missing the range parameter

D. Missing the FALSE parameter for exact match

Solution

1. Step 1: Check parameters in formula

   The formula has lookup_value, range, and column index but no match type.

2. Step 2: Identify default match behavior

   Without FALSE, VLOOKUP uses approximate match (TRUE) by default, which may give wrong results.

3. Final Answer:

   Missing the FALSE parameter for exact match -> Option D

4. Quick Check:

   Exact match requires FALSE as last argument [OK]

Hint: Always add FALSE for exact match in VLOOKUP [OK]

Common Mistakes:

• Assuming exact match is default
• Forgetting to add FALSE parameter
• Thinking quotes cause error

5.

You have a product list in A2:C6 with columns: Product, Category, Price.

You want to find the category of "Mango" using VLOOKUP. Which formula correctly returns the category?

hard

A. =VLOOKUP("Mango", A2:C6, 2, FALSE)

B. =VLOOKUP("Mango", B2:C6, 2, FALSE)

C. =VLOOKUP("Mango", A2:C6, 1, FALSE)

D. =VLOOKUP("Mango", A2:C6, 3, FALSE)

Solution

1. Step 1: Identify the column index for Category

   Columns are: 1=Product, 2=Category, 3=Price. Category is column 2.

2. Step 2: Check the range and lookup value

   Range A2:C6 includes Product in first column, so lookup works correctly.

3. Final Answer:

   =VLOOKUP("Mango", A2:C6, 2, FALSE) -> Option A

4. Quick Check:

   Category is column 2, so col_index=2 [OK]

Hint: Match column index to desired return column in VLOOKUP [OK]

Common Mistakes:

• Using wrong column index for category
• Using a range that does not start with lookup column
• Confusing column numbers with letters