FILTER, UNIQUE and SORT: an Excel interview question
List each customer once, A to Z, for the region someone picks, in one formula that updates itself. The dynamic-array answer interviewers look for, and a sheet that grades yours.
FILTER function
FILTER returns every value that meets a condition, as a list that spills down the sheet and updates by itself when the data changes. It's the formula version of the filter button.
Available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the webOn this page
FILTER syntax
=FILTER(array, include, [if_empty])| Argument | Required | What it means |
|---|---|---|
array | Yes | The range to return rows from: one column or several. |
include | Yes | A test with one TRUE/FALSE per row, such as B2:B7="East". Same height as array. |
if_empty | No | What to show when no row matches. Without it you get #CALC!. |
Examples
Every rep in one region
The result spills from E2 down as many rows as there are matches. The dashed cells are the spill: you type the formula once, in E2.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | East reps | |
| 2 | Ana | East | 1,200 | Ana | |
| 3 | Ben | West | 800 | Cara | |
| 4 | Cara | East | 450 | Eli | |
| 5 | Dan | North | 1,900 | ||
| 6 | Eli | East | 2,300 |
=FILTER(A2:A6,B2:B6="East") → AnaTwo conditions at once
Multiply the tests for AND: a row is kept only when both are TRUE. Add them, (test1)+(test2), for OR.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | East, over 1,000 | |
| 2 | Ana | East | 1,200 | Ana | |
| 3 | Ben | West | 800 | Eli | |
| 4 | Cara | East | 450 | ||
| 5 | Dan | North | 1,900 | ||
| 6 | Eli | East | 2,300 |
=FILTER(A2:A6,(B2:B6="East")*(C2:C6>1000)) → AnaCommon mistakes
- Something in the way of the spill. If any cell the result needs isn't empty, FILTER shows #SPILL!. Clear the cells below the formula.
- Using && or AND() for two conditions. AND() collapses the whole range into one TRUE or FALSE. Multiply the tests instead: (test1)*(test2).
- An include range of a different height. The test must have exactly one row per row of array, or FILTER returns #VALUE!.
FAQ
Can FILTER return several columns?
Yes. Give it a wider array, such as A2:C6, and every matching row spills with all its columns.
Why does FILTER show #CALC!?
No row matched and there's no if_empty argument. Add a third argument: =FILTER(A2:A6,B2:B6="South","No one yet") shows No one yet instead of the error.
How do I sort the filtered list?
Wrap it in SORT: =SORT(FILTER(A2:C6,B2:B6="East"),3,-1) sorts by the 3rd column, largest first.
Guides that use FILTER
Worked problems with practice sheets.
#SPILL! error in Excel: what it means and how to fix it
#SPILL! means a formula's result needs more cells than are free. Here's how to find what's in the way, the other causes of #SPILL!, and how to fix each one.
Know FILTER? Prove it on a real sheet.
Practise FILTER and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install