Return multiple columns
AdvancedIn analytics work, you rarely look up a single field. More often you need to pull a small “record” (multiple columns) for an ID, and you want it to stay maintainable as the dataset grows.
Your sheet contains a customer table in A9:E15. The lookup key is Customer ID (column A). The fields you need to return are Name, Email, Phone (columns B–D).
Your task
Write a single formula in C5 that:
- Uses the Customer ID in B3 as the lookup value
- Returns Name, Email, and Phone as a horizontal array that spills across C5:E5
- Pulls the values from the customer table (do not hardcode any customer details)
Your solution should keep working if the value in B3 changes to any other valid ID in the table.
Need some help?
Hint 1
XLOOKUP can return multiple columns if your return_array spans multiple columns (e.g., the Name/Email/Phone columns together).
Hint 2
Think: lookup_value = B3, lookup_array = the Customer ID column, return_array = the three adjacent columns you want back.
Hint 3
If you prefer INDEX/MATCH, INDEX can spill an entire row from a multi-column range when you use MATCH to find the row index.
Answer
Exercise
Return multiple columns
AdvancedIn analytics work, you rarely look up a single field. More often you need to pull a small “record” (multiple columns) for an ID, and you want it to stay maintainable as the dataset grows.
Your sheet contains a customer table in A9:E15. The lookup key is Customer ID (column A). The fields you need to return are Name, Email, Phone (columns B–D).
Your task
Write a single formula in C5 that:
- Uses the Customer ID in B3 as the lookup value
- Returns Name, Email, and Phone as a horizontal array that spills across C5:E5
- Pulls the values from the customer table (do not hardcode any customer details)
Your solution should keep working if the value in B3 changes to any other valid ID in the table.
Need some help?
Hint 1
XLOOKUP can return multiple columns if your return_array spans multiple columns (e.g., the Name/Email/Phone columns together).
Hint 2
Think: lookup_value = B3, lookup_array = the Customer ID column, return_array = the three adjacent columns you want back.
Hint 3
If you prefer INDEX/MATCH, INDEX can spill an entire row from a multi-column range when you use MATCH to find the row index.