Description
Filter a range or an array based on criteria you define.
NOTE
- The [include] argument must be a vector and have dimensions compatible with the array.
Syntax
Copied!=FILTER(array, include, [if_empty])
SAMPLE data 1
| | A | B | C | D |
|-----|--------------------------|------------|----------------------|---------------|
| 1 | Best phone model | Price ($) | Screen size (inches) | Battery (mAh) |
| 2 | Samsung Galaxy S24 Ultra | 1,299 | 6.8 | 5,000 |
| 3 | iPhone 15 Pro Max | 1,099 | 6.7 | 4,323 |
| 4 | Google Pixel 9 Pro XL | 1,099 | 6.7 | 5,060 |
| 5 | OnePlus Open | 1,699 | 6.3 and 7.8 | 5,000 |
| 6 | Samsung Galaxy Z Flip 6 | 1,099 | 6.7 | 4,000 |
| 7 | Google Pixel 8a | 499 | 6.1 | 4,492 |
| 8 | OnePlus 12 | 799 | 6.8 | 5,400 |EXAMPLE 1
Copied!//𝘨𝘦𝘵 𝘵𝘩𝘦 𝘭𝘪𝘴𝘵 𝘴𝘮𝘢𝘳𝘵𝘱𝘩𝘰𝘯𝘦𝘴 𝘸𝘪𝘵𝘩 𝘰𝘷𝘦𝘳 𝘰𝘳 𝘦𝘲𝘶𝘢𝘭 𝘵𝘰 5000𝘮𝘈𝘩 𝘣𝘢𝘵𝘵𝘦𝘳𝘺. =FILTER(A2:D8, D2:D8>=5000)
| | A | B | C | D |
|-----|--------------------------|------------|----------------------|----------------|
| 1 | Samsung Galaxy S24 Ultra | 1,299 | 6.8 | 5,000 |
| 2 | Google Pixel 9 Pro XL | 1,099 | 6.7 | 5,060 |
| 3 | OnePlus Open | 1,699 | 6.3 and 7.8 | 5,000 |
| 4 | OnePlus 12 | 799 | 6.8 | 5,400 |example 2
Copied!//𝘨𝘦𝘵 𝘭𝘪𝘴𝘵 𝘰𝘧 𝘴𝘢𝘮𝘴𝘶𝘯𝘨 𝘱𝘩𝘰𝘯𝘦𝘴. =FILTER(A2:D8, ISNUMBER(SEARCH("samsung", A2:A8)))
| | A | B | C | D |
|-----|--------------------------|------------|----------------------|----------------|
| 1 | Samsung Galaxy S24 Ultra | 1,299 | 6.8 | 5,000 |
| 2 | Samsung Galaxy Z Flip 6 | 1,099 | 6.7 | 4,000 |EXAMPLE 3
Copied!//𝘨𝘦𝘵 𝘭𝘪𝘴𝘵 𝘰𝘧 𝘴𝘢𝘮𝘴𝘶𝘯𝘨 𝘱𝘩𝘰𝘯𝘦𝘴 𝘮𝘪𝘯𝘪𝘮𝘶𝘮 5000𝘔𝘗𝘢. =FILTER(A2:D8, ISNUMBER(SEARCH("samsung", A2:A8))*(D2:D8>=5000))
| | A | B | C | D |
|-----|--------------------------|------------|----------------------|----------------|
| 1 | Samsung Galaxy S24 Ultra | 1,299 | 6.8 | 5,000 |SAMPLE DATA 2
| | A | B | C | D |
|:---:|:----------------:|:----------------:|:----------------:|:----------------:|
| 1 | 0789413121 | | | |
| 2 | 0789887798 | | | |
| 3 | 0789889777 | | | |
| 4 | 0798987877 | | | |
| 5 | 0787961146 | | | |
| 6 | 0864841338 | | | |
| 7 | 0346849681 | | | |
| 8 | 0777987979 | | | |
| 9 | 0934524124 | | | |
| 10 | 0879987879 | | | |
| 11 | 0987877989 | | | |
| 12 | 0354684684 | | | |
| 13 | 0569874634 | | | |EXAMPLE 1
Copied!//𝘳𝘦𝘵𝘶𝘳𝘯𝘴 𝘢 𝘭𝘪𝘴𝘵 𝘰𝘧 𝘯𝘶𝘮𝘣𝘦𝘳𝘴 𝘤𝘰𝘯𝘵𝘢𝘪𝘯𝘪𝘯𝘨 𝘣𝘰𝘵𝘩 7,8,9. =FILTER(A1:A13, BYROW(A1:A13, LAMBDA(A, COUNT(SEARCH({7, 8, 9}, A))=3)))
| | A | B | C | D |
|:---:|:------------------:|:------------------:|:-------------------|:-------------------|
| 1 | 0789413121 | | | |
| 2 | 0789887798 | | | |
| 3 | 0789889777 | | | |
| 4 | 0798987877 | | | |
| 5 | 0787961146 | | | |
| 6 | 0777987979 | | | |
| 7 | 0879987879 | | | |
| 8 | 0987877989 | | | |
| 9 | 0569874634 | | | |