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
-
2:13:05
The Nerd Realm
14 hours ago $6.26 earnedNew Years Eve! Fortnite Hunters w/ YOU! Creator Code: NERDREALM
68.4K5 -
11:04:44
FusedAegisTV
1 day agoNYE Eve! - 2025 Incoming 🎉 - 12hr Variety Stream!
133K7 -
1:18:52
Awaken With JP
17 hours agoSomehow The World DIDN’T End This Year! - LIES Ep 72
167K89 -
1:19:34
Michael Franzese
15 hours agoWhat 2024 Taught Us About the Future?
133K33 -
1:48:09
The Quartering
16 hours agoBird Flu PANIC, Sam Hyde DESTROYS Elon Musk & Patrick Bet David & Woke Witcher?
139K78 -
4:47
SLS - Street League Skateboarding
3 days agoLiz Akama’s 2nd Place Finish at SLS Tokyo 2024 | Best Tricks
61.7K6 -
4:06:54
LumpyPotatoX2
14 hours agoHappy New Year Rumble ! - #RumbleGaming
44.9K1 -
10:37
One Bite Pizza Reviews
1 day agoBest of Barstool Pizza Reviews 2024
69.8K32 -
2:37
Tate Speech by Andrew Tate
17 hours ago2025 WILL BE YOUR YEAR
119K44 -
3:51:31
Sgtfinesse
18 hours agoRumble New Years Eve with Sarge
69.5K