How To Filter A List In Excel Based On Another List

We can practise filter data by some sure criteria for a list or a table in excel. And if the criteria is another list option in worksheet, how can we filter information from original selected range refer to the new list choice? This article will provide you 2 methods to filter information based on some other list selection past COUNTIF Formula or Advanced Filter Function in excel, yous can select i you lot like to utilise it in your daily work.

Why we need to larn how to filter data based on some other listing selection? Actually, we often go a summary (filter range) from others and we just want to get part information past some criteria (criteria range, usually it is some other list), run into the example beneath.

Filter Data Based on Another List 1

In higher up table column A, B, C list all students' Names, IDs and Scores. Nosotros want to get ID and Score for class1 and class2 students, we need to filter data from the filter range based on the name list of class1 and class2. Now, yous tin follow below steps to filter data by dissimilar methods.

Method 1: Filter Information Based on Some other List Option past Formula

Step 1: Get-go, we need to find out which names included in cavalcade A likewise belong to Class1. We can utilise COUNTIF office to mark these names. In cell D2, enter the formula =COUNTIF($F$ii:$F$6, A2). And then click Enter. Verify that we go return value '1' in D2.

Filter Data Based on Another List 2

Filter Data Based on Another List 3

Stride 2: Drag the fill up handle down to fill all cells. So we tin find that names belong to class1 are marked with number '1' .

Filter Data Based on Another List 4

Stride iii: Create a filter on cavalcade D. Select on column D, click on Information->Filter, then click dropdown list, only cheque on number 'i', then click OK.

Filter Data Based on Another List 5

Verify that all names belong to class1 are listed properly with ID and Score.

Filter Data Based on Another List 6

Step four: Select all filtered data, click F5, on Get To dialog click on Special button, then cheque on 'Visible cells merely', and so click OK.

Filter Data Based on Another List 8

Stride v: Copy and paste the selected data to criteria range appropriately.

Filter Data Based on Another List 10

Filter Data Based on Another List 11

We can repeat above steps to filter data for class2.

Method ii: Filter Data Based on Another List Pick by Advanced Filter

Pace i: Select data you lot want to do filter, in this instance we select A2:C11, select Data->Advanced.

Filter Data Based on Another List 12

Pace ii: On Advanced Filter dialog, bank check on 'Filter the listing, in-place', in List range select $A$2:$A$11, in Criteria range, select $F$two:$F$6. Then click OK.

Filter Data Based on Another List 13

Filter Data Based on Another List 14

Footstep 3: Later above steps, names are filtered properly.

Filter Data Based on Another List 15

Footstep four: Repeat step#4-5 in method ane to copy and paste filtered information to criteria range.

Related Functions

Excel COUNTIF function
The Excel COUNTIF role will count the number of cells in a range that encounter a given criteria. This role can be used to count the different kinds of cells with number, date, text values, bare, not-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…