Arrays & ARRAYFORMULA › ARRAYFORMULA41 / 67

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

EasyFreeARRAYFORMULA

ARRAYFORMULA: join first and last names

Scout sign-ups at a junior high school in Bogor come in through a Google Form, with first and last names in separate columns. Mr. Hendra, the scout leader, needs a full-name column to print certificates.

Task

In C2, write one ARRAYFORMULA formula that joins the first name (A2:A7) and last name (B2:B7) with one space between them, for every row at once. The result spills into C2:C7.

Cells to fillC2:C7

Required functionsARRAYFORMULA

Expected result

Loading the expected result…

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

Hint 1
The & operator joins text. Inside ARRAYFORMULA it works on whole ranges at once, row by row.
Hint 2
Join three parts: the first-name range, a space " ", and the last-name range.
Hint 3
The shape is =ARRAYFORMULA(A2:A7 & … & B2:B7).

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

C2

=ARRAYFORMULA(A2:A7&" "&B2:B7)

Explanation

=ARRAYFORMULA(A2:A7&" "&B2:B7) calculates A2&" "&B2, A3&" "&B3 and so on in a single formula. You fill one cell and the rest fills itself. If a cell below C2 already has content, Google Sheets shows #REF! because the result cannot spill. Pro tip: in Google Sheets, press Ctrl+Shift+Enter while typing a formula to wrap it in ARRAYFORMULA automatically.

Starting worksheet: Sheet1
Row	A	B	C
1	First name	Last name	Full name
2	Ahmad	Fauzi
3	Putri	Maharani
4	Bagus	Saputra
5	Nur	Aini
6	Rizky	Pratama
7	Dewi	Anggraini
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result