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.
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.
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'
.
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.
Verify that all names belong to class1 are listed properly with ID and Score.
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.
Stride v: Copy and paste the selected data to criteria range appropriately.
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.
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.
Footstep 3: Later above steps, names are filtered properly.
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)…
How To Filter A List In Excel Based On Another List,
Source: https://www.excelhow.net/how-to-filter-data-based-on-another-list-selection-in-excel.html#:~:text=Step%201%3A%20Select%20data%20you,steps%2C%20names%20are%20filtered%20properly.
Posted by: mastersthearkly.blogspot.com
0 Response to "How To Filter A List In Excel Based On Another List"
Post a Comment