Formuladrill

Level 3: Lookups Exercise 54 of 118

Everyone in one department

An Excel formula exercise from Formuladrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.

The task

List everyone in the department named in E1 in one cell, E2, separated by a comma and a space, in the order they appear. For IT the result reads Ben, Dee, Gus.

What this exercise teaches

A lookup returns one match; FILTER returns all of them. On its own FILTER fills as many cells as there are matches, so here TEXTJOIN packs the result into a single cell.

A hint if you get stuck

FILTER(what to return, condition) keeps the rows where the condition is true. TEXTJOIN then joins the names it returns into one cell.

Functions used: FILTER, TEXTJOIN

Try it now, free, no account needed

Your first 15 exercises on Formuladrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.

Open this exercise

More exercises in Lookups