FILTER function
FILTER returns rows where a condition is true and spills the results into adjacent cells.
Lookup and reference
Where can you use it?
Reporting and operations teams. Use functions in this group to retrieve a field from an item, employee or order list, select the right record, or move values into another report.
Scenario in the workbook
Exclude transactions below the threshold. The remaining records keep their source order.
A worked example from the workbook
| Row | A | B |
|---|---|---|
| 6 | Source value | Output position |
| 7 | 30 | 1 |
| 8 | 10 | 2 |
| 9 | 20 | 1 |
=INDEX(FILTER($A$7:$A$9,$A$7:$A$9>=20),B7,1)Expected result30
See the inputs and instructions for all 3 examplesCopyable formulas
=FILTER(A2:C5,(B2:B5="L01")*(C2:C5>=7),"No records")=FİLTRE(A2:C5;(B2:B5="L01")*(C2:C5>=7);"Kayıt yok")Sample data
Enter the headers and sample records in A1:C5.
| Employee | Location | Hours |
|---|---|---|
| Deniz | L01 | 8 |
| Ada | L02 | 9 |
| Ege | L01 | 6 |
| Ekin | L01 | 7 |
Keep E2:G3 clear.
Enter the formula in E2.
To try the Turkish formula, use the sample data on the Turkish page.
Expected result
Deniz | L01 | 8 and Ekin | L01 | 7
How it works
Multiplying the conditions applies AND logic to each row. Occupied cells in the output area block the spill. Place the formula in a cell outside an Excel table.
Syntax
FILTER(array, include, [if_empty])
Compatibility
Excel for Microsoft 365, Excel 2021, and Excel 2024.
Related pages
Worked examples
Exclude transactions below the threshold. The remaining records keep their source order.
Open the English sheet. Inputs begin at A6. F7:F9 show the live results and G7:G9 show the original results for comparison.
| Row | A | B |
|---|---|---|
| 6 | Source value | Output position |
| 7 | 30 | 1 |
| 8 | 10 | 2 |
| 9 | 20 | 1 |
| Case | Formula | Expected result |
|---|---|---|
| Record 1 | =INDEX(FILTER($A$7:$A$9,$A$7:$A$9>=20),B7,1) |
30 |
| Record 2 | =INDEX(FILTER($A$7:$A$9,$A$7:$A$9>=20),B8,1) |
20 |
| Record 3 | =INDEX(FILTER($A$7:$A$9,$A$7:$A$9>=20),B9,1) |
30 |