Pull multiple columns for an employee (VLOOKUP)
You are building an employee profile card. Given an employee ID in F2, pull their Department into G2 and City into H2 from the master employee directory in columns A–D. Lock the table reference with $ so the lookup formula remains stable across columns.
Your solution should use cell references so it updates automatically when Test Case input values change.
Notes
When fetching multiple fields for the same ID, keep the table range locked ($A$2:$D$6) and the lookup value column-locked ($F$2 or $F2). Then increment col_index_num: column 3 for Department, column 4 for City.
Function guide
FAQ
How do I use VLOOKUP to return multiple columns?
Keep the lookup value ($F2) and table array ($A$2:$D$6) locked with $. In each output column, set col_index_num to the appropriate column number (3 for Department, 4 for City).
Can VLOOKUP return multiple columns automatically?
In modern Excel (Microsoft 365) and Google Sheets, you can pass an array constant like {3, 4}: =VLOOKUP(F2, A2:D6, {3, 4}, FALSE), which spills both columns at once.
What if an Employee ID is not found?
VLOOKUP returns #N/A. To handle missing entries cleanly, wrap the VLOOKUP in IFERROR or IFNA: =IFERROR(VLOOKUP($F$2, $A$2:$D$6, 3, FALSE), "Not Found").