Lookup and Reference functions are frequently used for processing data in a table. Today we will learn how to use the FILTER Function to filter data.

This function can be used to filter a range of data based on the criteria that you specify.

· Primary usage of the Filter function

Take this table as an example. Suppose we want to get the list of class 1, Select G3:G15, this is the cell range we want the result to return, so don't just select cell G3.

Enter the formula: =FILTER in the cell and press Tab.

Then we need to fill contents in the formulas.

The first parameter is Array, and we take the Name column as the range to filter.

The second parameter is Include, we select the Class column and select cell range A3:A15. It means to filter out the corresponding names according to the class. Enter =Class 1 after this parameter to make Class 1 the filter condition. Don't forget to add double quotes to the text.

Note: Dynamic array is not available in WPS Office. Please press Ctrl+Shift+Enter to make it an array. Otherwise, it will return a single numeric value.

At this time, we can get the student list of Class 1. But there is an error value #N/A in cell range G8: G15 because the number of the selected rows is more than the returned value. In this case, we can extract the data we need after filtering.

· Combine the Filter function with the drop-down list.

Take this form as an example. Suppose we want to filter out all the information of the award recipients. First, select cell F3, click the Insert drop-down list button in the Data tab. In the pop-up dialog box, enter the optionsYes and  No,click OK and we can get a simple drop-down list.

Then, select the area to return. Here, we want to return four columns of data. Select the cell range G3:J15, enter the formula =FILTER in the formula bar, and enter the Tab key.

The first parameter is the returned column, and we select the data area A3:D15. The second parameter should contain the condition, and we choose theAward Status column, that is, D3:D15. Following that, enter =F3, where we want to insert a drop-down list.

Don't forget to press Ctrl+Shift+Enter. Now, we can click the drop-down list button in cell F3 to filter out the information of awarded students.

Note: the #CALC error value may appear when using the FILTER function for multiple conditions,  due to the null result. How can we fix it?

In the FILTER function, we can enter the description for the null value in the third parameter. In this case, we enter Not found and press Enter. At this point, the error value becomes Not found as we set.

WPS Spreadsheet is a powerful chart and form processor. In WPS Academy, you can learn not only how to use Spreadsheet to sort out data but also how to corporate numeric information into graphs. By watching the step-by-step tutorials on WPS Academy, you will become a spreadsheet expert soon.