Excel Filter List Based on Another List | Formula and Advanced Filter Solutions

Download the featured file here: www.bluepecantraining.com/wp-...
In this Microsoft Excel video tutorial I explain how to filter a list based on another list. If you are using Excel 365 you can use this combination of functions: XMATCH, ISNUMBER and FILTER. If you have an older version of Excel you can use the Advanced Filter.
Video Table of Contents
00:00 Introduction
00:27 Use the FILTER function
02:52 Use the FILTER with tables
05:27 Use the Advanced Filter

[@Sephraes]

This solved a major issue for me. And taking it further, adding NOT() to the front of the ISNUMBER allowed me to find everything that was not on the lookup list. Thank you for your help on this.

[@mcvbeatz1241]

Huge help sir. You just helped me streamline a process. Great video

[@IvanCortinas_ES]

The FILTER function is a real genius created by Microsoft. Thank you Chester!!!

[@ElaineGilsdorf]

WOW!!! Thank you so much for explaining this.

[@laiwk2]

Awesome guide! save my days of work every month!

[@jondeaux336]

Chester, you've enabled me to look like I know what I am doing. Thank you for this tutorial. Subscribed.

[@user-qz5um1id7z]

Exactly what I'm looking for. Thanks for teaching the filter function.

[@yarnickmeek6584]

I have been looking for this all morning! Thanks!!!!

[@map929]

Clear and concise - helped me greatly. Thank you.

[@Chattmandoo]

Exactly what I worked on today, but a little more circumstantial. I have a list of names where some names have a cross in the next column. I want to add these names to a new list. This should be useful for me. Thank you for being so educational.

[@Jojosmith342]

Already subscribed from the first tutorial. Valuable lesson as always. Thumbs up from the beginning class so that I won't forget. Thanks a lot for your teaching ! 👍

[@user-jc3bg2xd5f]

you've saved my life

[@hhbadarin]

Thank you so much!

[@pietrobertolucci9305]

Fantastic. Great video Chester.

[@maheshlowe907]

Thanks for the video. Saved my day. Keep doing.

[@ravikiran2345]

Very helpful, thanks a lot

[@leixiao169]

Thank you very much for this tutorial, it helps me a lot!

[@Fxingenieria]

Excellent !!! Coud be =FILTER(B4:F57;COUNTIF(H4:H8;D4:D57))

[@__Est.her__]

I was looking on cutting down on the time it takes me to make my weekly reports. Using your tutoring it’s pretty much update the source data and send 🎉🎉 thanks

[@aravindarajchandrasekaran7797]

wow thanks , this is exactly im looking

[@selder_7]

Thanks so much. Was worried this wouldn’t work because my like columns were names and not numbers, but it did work. Good to know the different ways to do it and the advantages that come with them

[@jgsalins]

Very helpful!!!. How to filter data if the list of value are string pattern. For e.g. List of values are ABC, JKL etc, how to do string pattern match in PROD ID column. Thanks in advance!!!

[@amjadhasan7278]

For those using advanced filter, you can create a macro while using the advanced filter and create a button assigning that macro. Now whenever you make a change into the base data, you need to just click the button and it will do the job for you.

[@stevereed5776]

Hi Chester, thank you for the video, very useful. Is there a way to Filter only customers that have certain products. E.g Customer 1 has Product A & B, Customer 2 has product A & B but Customer 3 has product A, B & C, I want a list of customer that only have Product A & B. Any guidance would be useful. Thanks, Steve

[@crazyswimmer_6791]

I love you

[@JamieJackson-zb8wf]

Is there a way to use this function but with a sort option? I am using the following =SORT(FILTER(Data_planning,Date_end_date=A2)) but want to sort by a column from the copied data onto the new sheet? For example on the data I am coping I have a price column, I want to sort the copied data by price. Any tips would be greatly appreciated :-) Love your videos!

[@specialneedstoys]

Excellent! But can we retain all the Filtered rows as well as showing the Subtotals - rather like an expanded subtotal table using the old Data/Subtotal function? I am trying to create a Consolidated invoice (Brexit!) that shows all the stock items sorted by and sub-totaled (for weight for example) by Country of Origin.

[@boadiljones]

Hi. theres any video of dependent lists based on id columns instead of header columns? Meaning I got A columns with the id and b column (categoryid and category name) with the display text, and another table with the id and let's say productid and product name. that could be helpful. Thanks in advanced

[@focaltech3724]

Can you build an automated IT Inventory dashboard

[@gob2004]

0:31 Can you show how to filter base on multiple lists or columns?

[@johnathanreed98]

Side note for anyone who may run across this. Please make sure that your table names aren't the same as any of your function names. I had named one of my tables "Filter" And every single time I typed out the function and pressed enter it would replace the filter function with the filter table And would in turn produce a #REF! error.

[@ganlyb]

Very clever, however it would be so much easier to do in SQL

[@mohamedrafeek1392]

In above example, how to do combination of text, number with symbols. Like 2"-PSV-251441-X.

[@user-wg5ew5lx5f]

You could just use x lookup and hide na's