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

[@mohammednafiulislam6242]

Thank you very much for this tutorial, I searched a lot to solve this porblem which I use to face frequently. This is great, Thank you again

[@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.

[@user-jc3bg2xd5f]

you've saved my life

[@laiwk2]

Awesome guide! save my days of work every month!

[@ElaineGilsdorf]

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

[@pietrobertolucci9305]

Fantastic. Great video Chester.

[@IvanCortinas_ES]

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

[@yarnickmeek6584]

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

[@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.

[@mcvbeatz1241]

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

[@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.

[@map929]

Clear and concise - helped me greatly. Thank you.

[@hhbadarin]

Thank you so much!

[@maheshlowe907]

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

[@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 ! 👍

[@leixiao169]

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

[@aravindarajchandrasekaran7797]

wow thanks , this is exactly im looking

[@__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

[@ravikiran2345]

Very helpful, thanks a lot

[@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

[@Fxingenieria]

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

[@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.

[@crazyswimmer_6791]

I love you

[@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!!!

[@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.

[@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!

[@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

[@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.

[@focaltech3724]

Can you build an automated IT Inventory dashboard

[@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

[@gob2004]

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

[@mohamedrafeek1392]

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

[@ganlyb]

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

[@user-wg5ew5lx5f]

You could just use x lookup and hide na's