Why lookups connect related data in Excel - Challenge Your Understanding

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of this VLOOKUP formula?

You have a table where column A has product IDs and column B has product names. The formula =VLOOKUP(102, A1:B5, 2, FALSE) is entered in a cell. What will it return if the table is:

A1: 101, B1: Apple
A2: 102, B2: Banana
A3: 103, B3: Cherry
A4: 104, B4: Date
A5: 105, B5: Elderberry

A Apple

B Banana

C Cherry

D #N/A

💡 Hint

Look for the row where the first column matches 102 and return the value from the second column.

VLOOKUP searches the first column for 102 and returns the value from the second column in the same row, which is 'Banana'.

❓ Function Choice

intermediate

Which function correctly connects related data from two tables?

You have two tables: one with employee IDs and names, another with employee IDs and their departments. You want to find the department for a given employee ID. Which function is best to use?

A SUM()

B IF()

C VLOOKUP()

D COUNT()

💡 Hint

You need to find a matching value in one table and return related data from another.

VLOOKUP is designed to search for a value in the first column of a range and return related data from another column in the same row.

🎯 Scenario

advanced

How to avoid errors when lookup value is missing?

You use =VLOOKUP(200, A1:B5, 2, FALSE) but 200 is not in the first column. What will happen and how can you avoid showing an error?

A It returns a blank cell; use ISBLANK() to check.

B It returns 0; no action needed.

C It returns the last value in the column; use TRIM() to fix.

D It returns #N/A error; use IFERROR() to show a friendly message.

💡 Hint

Think about what happens when VLOOKUP can't find the value and how to handle errors gracefully.

VLOOKUP returns #N/A if the lookup value is missing. Wrapping it in IFERROR() lets you replace the error with a message or blank.

❓ data_analysis

advanced

What is the result of this INDEX-MATCH combination?

Given a table with columns A (ID), B (Name), and C (Score), what does this formula return?

=INDEX(B1:B5, MATCH(88, C1:C5, 0))

Assuming C1:C5 contains scores: 75, 88, 92, 88, 70 and B1:B5 contains names: Anna, Bob, Cara, Dan, Eva.

A Bob

B Dan

C Cara

D Anna

💡 Hint

MATCH finds the first exact match; INDEX returns the name at that position.

MATCH(88, C1:C5, 0) finds the first 88 at position 2; INDEX(B1:B5, 2) returns 'Bob'.

🧠 Conceptual

expert

Why are lookup functions essential for connecting related data?

Which statement best explains why lookup functions like VLOOKUP or INDEX-MATCH are important in spreadsheets?

A They allow you to find and combine data from different tables based on a common key, making data analysis easier.

B They automatically sort data alphabetically to improve readability.

C They create charts and graphs from raw data without formulas.

D They delete duplicate rows to clean up data.

💡 Hint

Think about how data from different lists or tables can be connected.

Lookup functions help find matching data in one table to bring related information from another, which is key for combining and analyzing data.