Why lookups connect related data in Excel - Why Use It

Introduction

Lookups help you find matching information from one list to another. They connect related data so you don't have to copy or type it again. This saves time and reduces mistakes.

When you have a list of product IDs and want to find their prices from another table

When you want to match employee names with their departments from a separate sheet

When you need to pull customer contact details based on their order numbers

When you want to combine sales data with region names stored in a different table

When you want to check if a student ID exists in the attendance list and get their status

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for typing

Step 2: Type

- the selected cell

Formula bar shows the typed formula

💡 Start typing =XLOOKUP( or =VLOOKUP( to begin the lookup formula

Step 3: Enter

- the lookup value (e.g., a product ID cell reference)

Formula bar shows the lookup value as first argument

Step 4: Select

- the lookup array or table where to find the lookup value

Formula bar shows the lookup array as second argument

Step 5: Select

- the return array or column with the related data you want

Formula bar shows the return array as third argument

Step 6: Type

- optional arguments like match mode or if not found message

Formula is complete and ready to run

Step 7: Press

- Enter key

Cell shows the related data connected by the lookup

Before vs After

Before

A list of product IDs in column A and a separate price list in columns D and E

After

Column B shows prices next to each product ID by connecting data from the price list using lookup

Settings Reference

Lookup value

📍 First argument in XLOOKUP or VLOOKUP formula

The value to find in the lookup table

Default: Cell reference

Lookup array or table

📍 Second argument in XLOOKUP or VLOOKUP formula

Where Excel searches for the lookup value

Default: Range of cells

Return array or column

📍 Third argument in XLOOKUP or VLOOKUP formula

Where Excel gets the related data to show

Default: Range of cells

Match mode

📍 Optional argument in XLOOKUP

How Excel matches the lookup value

Default: Exact match

If not found

📍 Optional argument in XLOOKUP

What Excel shows if no match is found

Default: Error

Common Mistakes

Using VLOOKUP with the lookup column not as the first column in the table

VLOOKUP only searches the first column of the table for the lookup value

Use XLOOKUP which can search any column or rearrange the table so the lookup column is first

Not locking the lookup table range with $ signs when copying the formula

The lookup range shifts when copying, causing wrong or no matches

Use absolute references like $D$2:$E$10 to keep the lookup range fixed

Forgetting to set exact match in VLOOKUP or XLOOKUP

Excel may return wrong or approximate matches if exact match is not specified

Always set the match mode argument to FALSE (VLOOKUP) or 0 (XLOOKUP) for exact matches

Summary

Lookups connect related data from different tables to save time and avoid errors.

XLOOKUP is the modern, flexible way to find and return matching data in Excel.

Remember to fix lookup ranges and use exact match to get correct results.

Practice

1. What is the main purpose of using a lookup function like VLOOKUP in Excel?

easy

A. To format cells with colors

B. To create charts and graphs automatically

C. To sort data alphabetically

D. To find and connect related data from different tables or lists

Solution

1. Step 1: Understand what lookup functions do

   Lookup functions like VLOOKUP search for a value in one table and return related data from another table.

2. Step 2: Identify the purpose of VLOOKUP

   VLOOKUP connects related data by matching values, making it easier to combine information from different places.

3. Final Answer:

   To find and connect related data from different tables or lists -> Option D

4. Quick Check:

   Lookup connects data = A [OK]

Hint: Lookups match values to connect data quickly [OK]

Common Mistakes:

• Thinking VLOOKUP creates charts
• Confusing lookup with sorting
• Assuming lookup changes cell colors

2. Which of the following is the correct syntax for a basic VLOOKUP formula to find a value in column 2 of a table located in A1:C10?

easy

A. =VLOOKUP(lookup_value, A1:C10, 2, FALSE)

B. =VLOOKUP(A1:C10, lookup_value, 2, TRUE)

C. =VLOOKUP(2, lookup_value, A1:C10, FALSE)

D. =VLOOKUP(lookup_value, 2, A1:C10, TRUE)

Solution

1. Step 1: Recall VLOOKUP syntax

   The correct syntax is VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).

2. Step 2: Match syntax to options

   =VLOOKUP(lookup_value, A1:C10, 2, FALSE) correctly places lookup_value first, then the table range, column number 2, and FALSE for exact match.

3. Final Answer:

   =VLOOKUP(lookup_value, A1:C10, 2, FALSE) -> Option A

4. Quick Check:

   Correct VLOOKUP syntax = C [OK]

Hint: Remember VLOOKUP order: value, table, column, exact match [OK]

Common Mistakes:

• Swapping lookup_value and table range
• Using TRUE instead of FALSE for exact match
• Putting column number in wrong place

3. Given the table below in cells A2:B5:

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

What will be the result of the formula =VLOOKUP("Mary", A2:B5, 2, FALSE)?

medium

A. 90

B. Mary

C. 85

D. #N/A

Solution

1. Step 1: Locate the lookup value in the table

   The formula looks for "Mary" in the first column (A2:A5). Mary is found in A3.

2. Step 2: Return the value from the 2nd column in the same row

   The 2nd column value in row 3 is 90, so the formula returns 90.

3. Final Answer:

   90 -> Option A

4. Quick Check:

   Mary's score = 90 [OK]

Hint: VLOOKUP returns value from specified column in matched row [OK]

Common Mistakes:

• Returning the lookup value instead of related data
• Using wrong column number
• Getting #N/A by using approximate match

4. You wrote the formula =VLOOKUP(B2, A2:C10, 3, TRUE) but it returns incorrect data. What is the most likely problem?

medium

A. The column index number 3 is too large

B. The lookup table is not sorted, causing approximate match errors

C. The lookup value is missing from B2

D. The formula should use FALSE instead of TRUE for exact match

Solution

1. Step 1: Understand the effect of TRUE in VLOOKUP

   TRUE means approximate match, which requires the first column of the table to be sorted ascending.

2. Step 2: Identify common cause of wrong results

   If the table is unsorted, approximate match can return wrong or unexpected data.

3. Final Answer:

   The lookup table is not sorted, causing approximate match errors -> Option B

4. Quick Check:

   Approximate match needs sorted table = B [OK]

Hint: Use FALSE for exact match to avoid sorting issues [OK]

Common Mistakes:

• Assuming TRUE always works without sorting
• Ignoring the need for sorted data with approximate match
• Confusing column index errors with match type

5. You have two tables: one with product IDs and names, and another with product IDs and prices. How can you use VLOOKUP to create a list showing product names with their prices?

hard

A. Use VLOOKUP to sum prices for each product

B. Use VLOOKUP to find product names by matching prices

C. Use VLOOKUP to find prices by matching product IDs in the price table

D. Use VLOOKUP to sort product names alphabetically

Solution

1. Step 1: Identify the common key between tables

   Product IDs appear in both tables and can be used to connect names and prices.

2. Step 2: Use VLOOKUP with product ID to get prices

   Apply VLOOKUP on the product ID from the name table to find matching prices in the price table.

3. Final Answer:

   Use VLOOKUP to find prices by matching product IDs in the price table -> Option C

4. Quick Check:

   Lookup by product ID connects names and prices = D [OK]

Hint: Match on product ID to combine related data [OK]

Common Mistakes:

• Trying to lookup prices by name instead of ID
• Using VLOOKUP to sort or sum data incorrectly
• Ignoring the need for a common key to connect tables