ExcelFlux

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

RowAB
6Source valueOutput position
7301
8102
9201
Record 1
=INDEX(FILTER($A$7:$A$9,$A$7:$A$9>=20),B7,1)

Expected result30

See the inputs and instructions for all 3 examples

Copyable formulas

English Excel · en-US
=FILTER(A2:C5,(B2:B5="L01")*(C2:C5>=7),"No records")
Turkish Excel · tr-TR
=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.

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
Microsoft Excel reference