MO-201 Question #6: Real Exam Question with Answer & Explanation

Question

On the 'Employees' worksheet, in cell C4, enter a formula that returns the office phone number from the 'Contact_Information' cell range using an exact match for the 'Contact_Information' id.

Explanation

Excel Lookup Formula - Explanation

Overall Goal

You need to retrieve a specific piece of data (office phone number) from one table (Contact_Information) and display it in another worksheet (Employees). This is a classic lookup problem - you have an ID that exists in both places, and you use it as the key to pull matching data across.

The correct tool is VLOOKUP (or INDEX/MATCH), using exact match mode so you get the precise record, not an approximation.

The Formula

=VLOOKUP(B4, Contact_Information, [phone_col_number], FALSE)

(The exact column number depends on the structure of Contact_Information.)

Step-by-Step Breakdown

1. Start with =VLOOKUP(

VLOOKUP scans the first column of a range for a value, then returns data from a specified column in that same row. It's the standard function for "find a row by ID, return a field from it."

Skipping this and using a simple cell reference would just copy a value - it wouldn't perform a lookup at all.

2. First argument - the lookup value (e.g., B4)

This is the employee ID from the Employees sheet - the value you're searching for inside Contact_Information. Cell B4 (or whichever cell holds the ID) must match the IDs in the first column of Contact_Information.

Wrong cell reference here means you look up the wrong employee, returning someone else's phone number.

3. Second argument - Contact_Information (the named range)

This is the table to search. Using the named range (rather than a raw range like Sheet2!A:D) makes the formula readable and automatically adjusts if the range is redefined.

Using the wrong range means VLOOKUP searches the wrong data entirely.

4. Third argument - the column index number

This tells VLOOKUP which column of Contact_Information to return. If the phone number is in the 3rd column of that range, you use 3. Count columns from the left edge of the named range, not from column A of the sheet.

Wrong column number returns the wrong field (e.g., email instead of phone).

5. Fourth argument - FALSE (exact match)

FALSE means VLOOKUP requires a perfect ID match. TRUE would allow approximate matching, which assumes the first column is sorted and can silently return the wrong record.

Omitting this argument defaults to TRUE in some versions - a dangerous behavior for ID-based lookups that can produce incorrect results without any error message.

What Goes Wrong Without Exact Match

Exact match is always correct for ID/code lookups. Approximate match is only appropriate for range lookups (e.g., tax brackets).

Memory Tip

Think of VLOOKUP's four arguments as a sentence:

"Look up [this value] in [this table], return column [N], exact? [TRUE/FALSE]"

And remember: FALSE = Faithful - it faithfully finds only an exact match.

No community discussion yet for this question.