Menu
EXCEL FILTER FUNCTION
Excel FILTER Function Introduction
The FILTER function returns a subset of items from a range.
Description of Excel FILTER Function
Syntax :
=FILTER (array, include, [if_empty])
Parameters :
- array is the range containing the items to be returned;
- include is an expression defining the condition(s);
- and [if empty] optionally returns a specific value if nothing is found.
Step By Step Guide of FILTER Function
Example :
Let’s create a dataset of student information that includes their ID, Name, Department, Semester, and CGPA. Now we’ll look for data where the Department is CSE and the CGPA is more than 3.77. According to AND logic, both criteria must be true in order to obtain knowledge from a pupil.
The table range is B4:F14 in this case.
(D4:D14=I4) * (F4:F14>=I5) is the condition where D4:D14=I4 finds the rows where Department = CSE and F4:F14>=I5 finds the rows where CGPA is more than or equal to 3.77.
When there is no matched data, the message “No results” will be displayed.
Key Points
#VALUE – It will arise if the widths of the array and include argument are conflicting.
#CALC! – If the optional if empty argument is ignored and no results matching the criteria are obtained, this message will be shown.
#NAME – When you try to use FILTER in an older version of Excel, this message will occur.
#REF! – This problem occurs when a FILTER formula is used between two workbooks and the source workbook is closed.
or #N/A #VALUE This sort of error can arise if a value in the include argument is incorrect or cannot be converted to a Boolean value (0,1 or TRUE, FALSE).
Related Functions
HLOOKUP Function
HLOOKUP Function
Explore NowVLOOKUP Function
VLOOKUP Function
Explore NowINDEX Function
INDEX Function
Explore NowMATCH Function
MATCH Function
Explore NowDATE Function
DATE Function
Explore NowIF Function
IF Function
Explore NowOR Function
OR Function
Explore NowAND Function
AND Function
Explore NowRANK Function
RANK Function
Explore NowSUMIF Function
SUMIF Function
Explore NowXIRR Function
XIRR Function
Explore NowWORKDAYS Function
WORKDAYS Function
Explore Now
