Premium Only Content
Excel FILTER Function TRICK for Non Adjacent Columns
6 August 2020 Excel Hacks: Tips & Tricks
Are you up for a really cool trick with Excel's new Filter Function? With a simple trick, you can use it to get multiple match results from Non-Adjacent columns in a Simple & Dynamic way. You can also use this trick to get unique values from non-adjacent columns.
So, let's say we have a dataset with 6 columns but we're only interested in the information in column 2 and 5, i.e. in non-adjacent columns. Plus, we don't want to get "all" value from these columns but instead only records that match a certain criteria. And we want it to be in a dynamic way so whenever we change the criteria, the result updates automatically.
I'll show you an Excel trick that get's this done in record time. The secret is to broadcast array constants in our formula. And not only that: You can also use this to get a UNIQUE list of combinations that are in non-adjacent columns.
As a bonus tip I'll show you how you can SORT the result based on a value that's not in the filtered end result.
Many thanks to my student Adam Payne for sharing this method with me!
*** In case you'd like to learn more about Excel Dynamic array functions check out my complete course: https://courses.xelplus.com/p/excel-dynamic-array-formulas
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-filter-trick-non-adjacent-columns
LINK to FILTER video: https://youtu.be/Eehk6PC0oGs
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
Time Stamps
00:00 Multiple Match Results from Non-Adjacent Columns
01:40 Excel FILTER Function
04:00 Filter Out the Columns You Do NOT need
06:09 Get UNIQUE list from Non-Adjacent Columns
09:10 Bonus Trick for Sorting
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel #Excel #LeilaGharani
-
1:46:16
Redacted News
8 hours agoRFK CONFIRMATION: Kennedy goes to WAR with Big Pharma Democrats in Fiery Hearing | Redacted Live
201K396 -
57:31
Candace Show Podcast
8 hours agoBREAKING! Taylor Swift Turns Against Blake Lively & Ryan Reynolds | Candace Ep 141
193K195 -
1:04:59
Sarah Westall
5 hours agoRFK Jr Report, Constitution Suspended, War Time Procedures in Place, WHO Exit, DOD w/ Sasha Latypova
50.1K32 -
1:56:37
Melonie Mac
9 hours agoGo Boom Live Ep 35!
48.5K13 -
1:01:13
LFA TV
13 hours agoPRESIDENT TRUMP SIGNS LAKEN RILEY ACT | BASED AMERICA 1.29.25 6pm
59K7 -
1:43:07
2 MIKES LIVE
6 hours ago2 MIKES LIVE #172 News Breakdown Wednesday!
28K2 -
1:26:16
The Big Migâ„¢
8 hours agoJ6’r Ryan Samsel Free At Last The BOP & DOJ Exposed
28.3K3 -
31:34
The Based Mother
8 hours ago $1.45 earnedEXECUTIVE ORDER MUTILATES GENDER IDEOLOGY! Trump’s pulling out all the stops.
17.8K8 -
1:05:44
The Amber May Show
1 day ago $1.39 earnedSaving The Innocence | Rescuing Children From Rape Trafficking | Alan Smyth
22.1K3 -
1:40:46
In The Litter Box w/ Jewels & Catturd
1 day agoIs the Jab Poisonous? | In the Litter Box w/ Jewels & Catturd – Ep. 730 – 1/29/2025
88.3K56