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.
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 exerciseMore exercises in Lookups
- Find the price
- Look left with INDEX and MATCH
- When the code does not exist
- Tax bracket lookup
- Finishing position
- Price grid
- Return from the left
- Price list into an order
- A lookup inside IFERROR
- Who sold the most
- A column number that moves
- Commission tiers with XLOOKUP
- The most recent price
- INDEX and MATCH on two conditions
- Third place