Lookup & reference › XLOOKUP89 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

MediumFreeXLOOKUP

XLOOKUP: return three columns at once

An HR officer at a garment factory in Sukoharjo is often asked for employee details over the phone. She wants to type an employee ID into one cell and have that employee's name, department and phone number appear next to it at once.

Task

In H2, write a single XLOOKUP formula that finds the employee ID in G2 in column A and returns columns B to D at once, so the result spills into H2:J2.

Cells to fillH2:J2

Required functionsXLOOKUP

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
The return argument of XLOOKUP can span several columns, such as B2:D9. The result is one row with three values.
Hint 2
Write the formula in H2 only; I2 and J2 are filled by the spill. Make sure those two cells are empty.
Hint 3
Pattern: =XLOOKUP(G2, A2:A9, …).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

H2

=XLOOKUP(G2,A2:A9,B2:D9)

Explanation

=XLOOKUP(G2,A2:A9,B2:D9) returns one row from three columns (name, department, phone) and spills it into H2:J2. With VLOOKUP you would need three formulas with column numbers 2, 3 and 4. Make sure the lookup column and the return range have the same number of rows, or the result is #VALUE!. Try changing the ID in G2: all three columns update.

Starting worksheet: Sheet1
Row	A	B	C	D	G	H	I	J
1	Emp. ID	Name	Department	Phone	Find ID	Name	Department	Phone
2	GM-0112	Sri Wahyuni	Sewing	0812-2671-4410	GM-0141
3	GM-0127	Agus Setiawan	Cutting	0857-1203-8842
4	GM-0135	Lina Marlina	QC	0813-9045-2276
5	GM-0141	Bayu Nugroho	Warehouse	0821-3388-1057
6	GM-0158	Dewi Anggraini	Sewing	0896-7712-3304
7	GM-0163	Rudi Santoso	Packing	0878-5530-6619
8	GM-0170	Yanti Susilo	Cutting	0819-4421-7783
9	GM-0184	Eko Prasetyo	Mechanic	0838-2290-1145
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result