Keep the rows for one region with FILTER
Step 1 of 3
VLOOKUP and XLOOKUP stop at the first match. FILTER returns every row that passes a test, here every row where the region is North.
Click Continue when you're ready.
Already know it? Type the formula straight into E2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Sales | Rep | Sales | ||
| 2 | North | Ann | 420 | ||||
| 3 | South | Ben | 310 | ||||
| 4 | North | Cy | 150 | ||||
| 5 | West | Di | 500 | ||||
| 6 | South | Ed | 260 | ||||
| 7 | North | Fay | 380 | ||||
| 8 |
Need help?
Why this works
The test A2:A7="North" is TRUE in rows 2, 4 and 7. FILTER keeps those rows of B2:C7 and spills them into E2:F4, so one formula fills six cells. Add a North row to the data and the list grows by itself.
Common mistake: Giving the second argument as "North" instead of a test, A2:A7="North". FILTER needs TRUE or FALSE for each row.
The answer
- E2
=FILTER(1B2:C7,2A2:A7="North")
In plain words: Return 1the reps and sales in B2:C7 on the rows where 2the region in A2:A7 is North.
It returns every row of a range where a test is TRUE, for example =FILTER(B2:C7,A2:A7="North"). The result spills into the cells below.
Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. Google Sheets has FILTER too.
+15 points