How to use multiple criteria in the FILTER function

Ever wanted to return a subset of a dataset rather than the whole thing? The FILTER function lets us sieve a table to extract only the desired rows based on criteria.

Check out this quick video to learn how to do this!

In the example, a table called tblProducts houses product data, and we want to return only the items that cost under £100.

This formula achieves the result:

=𝙵𝙸𝙻𝚃𝙴𝚁(𝚝𝚋𝚕𝙿𝚛𝚘𝚍𝚞𝚌𝚝𝚜,
𝚝𝚋𝚕𝙿𝚛𝚘𝚍𝚞𝚌𝚝𝚜[𝙿𝚛𝚒𝚌𝚎 (£)]<𝟷00
)

The table name is referenced in 𝚊𝚛𝚛𝚊𝚢 to return all columns, whilst the condition is specified in 𝚒𝚗𝚌𝚕𝚞𝚍𝚎 to return all rows where the value in the Price column is less than 100.

The condition evaluates to an array of TRUE and FALSE values that represent the rows and indicate which ones to return:

{𝚃𝚁𝚄𝙴;𝙵𝙰𝙻𝚂𝙴;𝚃𝚁𝚄𝙴;𝚃𝚁𝚄𝙴;𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;
𝚃𝚁𝚄𝙴;𝚃𝚁𝚄𝙴;𝙵𝙰𝙻𝚂𝙴;𝚃𝚁𝚄𝙴;𝚃𝚁𝚄𝙴;𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;
𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;𝙵𝙰𝙻𝚂𝙴;𝚃𝚁𝚄𝙴;𝚃𝚁𝚄𝙴}

Often, you're likely to require more than one criterion, though. The function provides no obvious way of doing this. However, there is a way:

=𝙵𝙸𝙻𝚃𝙴𝚁(𝚝𝚋𝚕𝙿𝚛𝚘𝚍𝚞𝚌𝚝𝚜,(𝚝𝚋𝚕𝙿𝚛𝚘𝚍𝚞𝚌𝚝𝚜[𝙿𝚛𝚒𝚌𝚎 (£)]<𝟷00)*(𝚝𝚋𝚕𝙿𝚛𝚘𝚍𝚞𝚌𝚝𝚜[𝚁𝚊𝚝𝚒𝚗𝚐]>=𝟺))

Building on the same condition as the first example, an asterisk is added after, which is the multiplication operator representing AND criteria. The second condition then returns all rows with a value greater than or equal to 4 in the Rating column.

This calculation is taking place:

{𝟷;0;𝟷;𝟷;0;0;𝟷;𝟷;0;𝟷;𝟷;0;0;0;0;0;0;0;𝟷;𝟷}
*
{0;𝟷;𝟷;0;𝟷;0;0;𝟷;0;0;0;𝟷;0;0;0;𝟷;0;𝟷;𝟷;0}

Because two conditions are multiplied, the TRUE and FALSE array values are coerced into 1s and 0s. Multiplying by 0 is always 0, so both respective values must equal 1 for their row to be returned.

Finally, if using multiple conditions, be sure to enclose each one in brackets to avoid any unusual results.

Like this guide? Check out our Video Tutorials to discover other ways to power up your Excel skills!