The Excel Formula I Can’t Live Without

Learn how to use the FILTER function as an alternative to other lookup formulas like VLOOKUP or XLOOKUP. FILTER returns multiple values with one single formula. It's a versatile function that every Excel user should know. We start with the basics and progress to more advanced techniques for non-adjacent columns, copying the formula down, and multiple criteria.

🔗LINKS


Related Content:

0:00 Introduction
0:15 Basic FILTER Formula
1:24 Error Handling
3:03 Drop Down List
3:34 Formatting the Spill Range
3:54 Common Error + Excel Tables
5:07 Welcome + Training Courses
5:31 Non-Adjacent Columns
6:28 Copy Down Formula
7:32 Choose Rows
9:14 Show Details Checkbox
9:30 Multiple Criteria
10:38 AND Logic Explained
11:54 OR Logic Explained
12:50 Pro Tip
13:19 What's Next

[TheBtrivedi]

Found your Channel Today. This video looks like herbs and therapy to me. Great upload. Thank you so much. ❤ Looking forward for more.

[PRHC]

Dude! All do respect...lol. You saved my hide. Been trying to figure out how to do this with multiple criteria into one cell for about two weeks now. I was not able to accomplish this through any other video. My friend this is amazing. Can't thank you enough. Pass the collection plate around for this one...lol.

[PaulEBrownbill]

Excellent filter function, liking the and or logic tips, and hard coding as much of the formula as possible. Paul

[chazhinkeldey]

Wow! Thanks for this! I literally needed it last night and walked in this morning and had this video in my inbox!!

[chrism]

Nice video and very well-explained, thanks Jon!

[deepakmirchandani]

great video sir. thanks.
further how to use filter function for approximate match ?. please help

[IvanCortinas_ES]

Excellent tutorial, as always, Jon. Very interesting example using ARRAYTOTEXT combined with FILTER. Thanks for sharing.

[bennyjulong]

Awesome ! It's very useful tip to me for my daily routine work Thanks a lot ❤

[heatherr]

GREAT examples, and very clear explanation - thanks as always Jon. This is going into my current set of review files to help me remember this new way to do things.

[michaelmalone]

Thank you Jon. That was very well explained. I feel like an expert :)

[kellershouse]

When you are using the ArrayToText could you use an ALT ENTER to delimit the results to the same cell but with a formatted wrap text. So the cells would expand with multiple values?

[angelaisland]

Great information as always! Thanks Jon!

[potatocraftd]

I use this filter function for dynamic live reports

[richardhay]

One problem with CHOOSECOLS/ROWS is that they don't accept cell references. If you add or delete rcolums/rows the formulas break. Also clicking on a column name is more convenient than counting columns in a many-column data set

[tomjun]

Very helpful video! Thank you for your content.

[nicolebrocke]

Thank you for this video! I was just trying to use this function for the first time today! Perfect timing! Is there a way to use the filter function with the sort function AND sort the data in a custom order (not ascending or descending)?

[SandeepTiwari-dpwx]

Hello Jon, thanks this is really very helpful.

But how to get details if my required column are not in a sequence. eg. Contact Name is column c and phone number is column h...so on.

Please help.. thank you 🙏

[alcol-]

Is it just me or 0+1 should equal 1 at 12:18? Anyways, thanks Jon for another great video!

[WaqasAhmed-gjsq]

Which software you use for video effects, editing and transitions?

[vinc.]

Very useful! Is it possible to have the timestamps?