Which VLOOKUP formula, when entered in cell L2 and dragged through L5, looks…
2023
Which VLOOKUP formula, when entered in cell L2 and dragged through L5, looks up each representative ID from column J in the same fixed source table A2:C7 and returns the corresponding Avg. Number of Calls when a match exists?
A | B | C | J | K | L |
|---|---|---|---|---|---|
Representative ID Number | Avg. Call Length (Mins.) | Avg. Number of Calls | Representative ID Number | Hire Date | Avg. Number of Calls |
41136 | 93 | 1392 | 44389 | 9/29/19 | |
41888 | 64 | 1324 | 41888 | 9/24/19 | |
42434 | 75 | 1295 | 42886 | 6/10/15 | |
42886 | 83 | 1360 | 44136 | 1/1/19 | |
43027 | 49 | 1269 | |||
44999 | 70 | 1088 |
Answer: C. =VLOOKUP(J2, A$2:C$7, 3, FALSE) — ConceptVLOOKUP uses the form VLOOKUP(lookup_value, table_array, col_index_num, FALSE) for an exact match. The lookup value must be searched in the first…
- A.
=VLOOKUP(A2, J2:L5, 1, FALSE)
- B.
=VLOOKUP(J2, A$2:C$7, 1, FALSE)
- C.
=VLOOKUP(J2, A$2:C$7, 3, FALSE)
- D.
=VLOOKUP(J2, A2:C7, 3, FALSE)
Show answer & explanation
Correct answer: C
Concept
VLOOKUP uses the form VLOOKUP(lookup_value, table_array, col_index_num, FALSE) for an exact match. The lookup value must be searched in the first column of the table array, and the column index counts from that first column. Dollar signs keep the table-array row bounds fixed when the formula is filled down.
Application
Use J2 as lookup_value because column J contains the representative ID to be looked up.
Use A$2:C$7 as table_array: representative IDs are in its first column A, average calls are in its third column C, and the dollar signs keep rows 2 through 7 fixed while filling down.
Use col_index_num 3 to return the value from column C, and FALSE to require an exact ID match.
Therefore the formula is =VLOOKUP(J2, A$2:C$7, 3, FALSE). When filled down, J2 becomes J3, J4, and J5, while A$2:C$7 remains fixed.
Cross-check and contrast
For representative ID 41888, the fixed A:C table returns 1324 from column C. An ID absent from column A produces #N/A under exact matching; this does not change which formula has the required lookup direction, return column, and fixed table rows.
=VLOOKUP(A2, J2:L5, 1, FALSE) reverses the lookup direction, returns the first column of J:L, and lets the table range move when filled down.
=VLOOKUP(J2, A$2:C$7, 1, FALSE) uses the required fixed table but returns column A, the representative ID.
=VLOOKUP(J2, A$2:C$7, 3, FALSE) uses the column-J ID, keeps rows 2 through 7 fixed, and returns column C.
=VLOOKUP(J2, A2:C7, 3, FALSE) returns column C at L2, but its table shifts to A3:C8, A4:C9, and A5:C10 as the formula is filled down.