The Excel Formula I Can’t Live Without

[@PRHC38]

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.

[@ExcelCampus]

Glad it helped. 😀

[@PRHC38]

@@ExcelCampus absolutely! One thing however. If possible please add how to format using the “unique” function. I was able to create the formula based on what you have here but then I saw that I had duplicates. Then I remembered to use “unique” in order to correct that. Other than that this tutorial is perfect! Thanks again.

[@ExcelCampus]

Great suggestion, Toure! For anyone else reading this, you can wrap FILTER in UNIQUE to remove duplicates on the results of FILTER. =UNIQUE(FILTER()) I'm planning to do a follow-up video on FILTER based on all the great questions and suggestions in the comments here. I'll include this example. Thanks again and have a nice day! 🙂

[@TheBtrivedi]

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

[@chazhinkeldey2362]

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

[@ExcelCampus]

Amazing! Thanks for sharing Chaz. I'm happy to hear it helped. 🙏

[@PaulEBrownbill]

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

[@heatherr5191]

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.

[@ExcelCampus]

Thanks Heather! I'm happy to hear you'll be adopting these techniques. 😊

[@IvanCortinas_ES]

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

[@ExcelCampus]

You're welcome! 😀

[@rrki]

Great material. Thanks John

[@boaznechmad]

Wow!!!!!! you r amazing!!!!

[@chrism9037]

Nice video and very well-explained, thanks Jon!

[@ExcelCampus]

Thanks Chris! 🙌

[@bbotzong]

Nice tutorial, Jon. Thanks for sharing!!!!!

[@ExcelCampus]

Thanks Bill! 🙌

[@bennyjulong2033]

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

[@ExcelCampus]

I'm happy to hear it. Thanks Benny! 🙌

[@nicolebrocke8989]

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)?

[@ExcelCampus]

Great question, Nicole! Yes, it's definitely possible. When you say custom order, do you want to sort ascending/descending based on another existing column in the source table?

[@nicolebrocke8989]

Another column in the source table, yes. However, not ascending or descending but rather a specific custom order.

[@ExcelCampus]

Thanks for clarifying. So, yes, this is possible using the SORTBY function. We need to sort both the array and include arguments withing FILTER first. Basically, we sort then filter. This means SORTBY must be used twice. So the formula looks something like the following. =FILTER(SORTBY(),SORTBY()=B4) This will return the sorted arrays to filter, and filter criteria "=B4" can be applied to the sorted array. To do a custom sort we can use the XMATCH function to look up and return the row number on the custom sorted list. Here's an example formula from the Tables example in the video at 3:54. =FILTER(SORTBY(tblContacts3[[Contact Name]:[State]],XMATCH(tblContacts3[State],P7:P14)),SORTBY(tblContacts3[Customer Name],XMATCH(tblContacts3[State],P7:P14))=B4) I have a unique list of the States in a custom order in P7:P14. XMATCH is looking up each value in the state column in that list and returning the row number to SORTBY. I'm planning a follow-up video on advanced techniques for FILTER and I'll include this example. I hope that helps. Thanks again and have a nice day! 🙂

[@nicolebrocke8989]

This is very helpful! Thank you for taking the time to respond! I look forward to watching your follow up video.

[@michael6malone]

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

[@angelaisland4036]

Great information as always! Thanks Jon!

[@ExcelCampus]

Thanks Angela! 🙂

[@warrenanderson412]

As always...great content!

[@deepakmirchandani1348]

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

[@tomjun8945]

Very helpful video! Thank you for your content.

[@ExcelCampus]

You're welcome! 😀

[@OneToothedWonder]

Great tutorial. TYSM.

[@KrisArmstrong-n9o]

Love! Love! Love! ❤❤ Thanks!

[@ExcelCampus]

Thanks Kris! 😊

[@virginia555]

Super useful, thank you

[@ExcelCampus]

Thanks Daniel! 🙂

[@yyusof78]

Marvelous mr Jon

[@alcol72-0]

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

[@ExcelCampus]

You are correct. Typo during the editing process. Sorry about that! Thanks for letting us know. 🙌

[@mikolajs6067]

great! thank you

[@potatocraftd6629]

I use this filter function for dynamic live reports

[@vinc.7765]

Very useful! Is it possible to have the timestamps?

[@ExcelCampus]

Thanks for the reminder, Vin! We just added the timestamps for chapters. 👍

[@35jays]

Thanks!!

[@WaqasAhmed-gj3sq]

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

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

[@ExcelCampus]

Great question, Kent! Yes, there are few ways to go about this. You can use the CHAR(10) function as a delimiter for TEXTJOIN. Use TEXTJOIN instead of ARRAYTOTEXT so we can specify the delimiter. The 10 in CHAR(10) returns a line break as the delimiter between each value. The formula looks like the following. =TEXTJOIN(CHAR(10),TRUE,FILTER(tblContacts6[[Contact Name]:[Phone Number]],tblContacts6[Customer Name]=B4)) You must apply Wrap Text formatting to the cell and increase the row height to see the results on multiple lines within the cell. Since the array for FILTER contains two columns, the results will look like the following. Ketty 5552264675 Justine 5552879911 Inger 5553794109 Jimmy 5554385379 If you want to get both name and phone number on the same line, then you can use BYROW with LAMBDA to first join each row with a delimiter, then join the rows with a line break. Here is what that formula looks like. =TEXTJOIN(CHAR(10),TRUE,BYROW(FILTER(tblContacts6[[Contact Name]:[Phone Number]],tblContacts6[Customer Name]=B4),LAMBDA(row,TEXTJOIN(", ",TRUE,row)))) And the results will look like the following. Ketty, 5552264675 Justine, 5552879911 Inger, 5553794109 Jimmy, 5554385379 I hope that helps. I'll do a follow up video on these techniques. I think they are great ones to know for certain scenarios.

[@SandeepTiwari-dp2wx]

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 🙏

[@ExcelCampus]

Hi Sandeep, Great question! You can still use CHOOSECOLS and specify the column numbers out of order. For example, you could use 1,4,2 for the column numbers to return the State column before the Phone Number column. I talk about CHOOSECOLS at 5:31 in the video. I hope that helps. Thanks again and have a nice day! 🙂

[@you_are_kidding_me_right]

Can a drop down box be done for filtering contacts where you can choose from the drop down?

[@ExcelCampus]

Great question! Yes, I talk about adding a drop-down list at 3:03 in the video. You can use the same technique for filtering contacts instead of customer/company names. If you want to only display contacts in the drop-down for the selected customer, this can also be done. It's typically referred to as dependent drop-down lists. I'm planning a follow-up video on advanced techniques for FILTER and I'll include this one as an example. I hope that helps. Thanks again and have a nice day! 🙂

[@you_are_kidding_me_right]

@@ExcelCampus Thanks!!!💯

[@richardhay645]

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

[@ExcelCampus]

Great point, Richard! We can use the XMATCH function within CHOOSECOLS to help prevent this issue. The formula would look like the following. =FILTER(CHOOSECOLS(tblContacts3[[Contact Name]:[State]],XMATCH(C3:E3,tblContacts3[[#Headers],[Contact Name]:[State]])),tblContacts3[Customer Name]=B4) XMATCH will look up all three header values in C3:H3 in the table that contains the source data, and return an array of column numbers {1,2,4} to CHOOSECOLS. This allows for columns to be inserted/deleted and also more flexibility with what is displayed in the results. I'll do a follow up video on more advanced techniques and include this one. Thanks again and have a nice day! 🙂

[@richardhay645]

@ExcelCampus yes!! I'm down for a video on as many advanced topics with FILTER as you can come up with. I'm also down with Microsoft modifying FILTER to accept cell references. Finally I'm down with trying CoPilot to see if it can duplicate your EXMATCH construction to save me a bunch of keystroks!! Thanks for your help.

[@viktorasgolubevas]

@@ExcelCampus Great, Jon! Actually, in most cases of "CHOOSECOLS/XMATCH" we could reference just a table instead of a structured diapason =FILTER(CHOOSECOLS(tblContacts3, XMATCH(C3:E3, tblContacts3[#Headers])), tblContacts3[Customer Name]=B4)

[@viktorasgolubevas]

@@ExcelCampus Also, in case your target non-adjacent header is an ordered (important) subset of the source header, I'm using COUNTIF technique - demo of 2-dimensional slice and dice with FILTER :)) =FILTER(FILTER(tblContacts3, COUNTIF(C3:E3, tblContacts3[#Headers])), tblContacts3[Customer Name]=B4)

[@rihanfz9900]

👍

[@oluwapelumiaroboto5382]

Please share the link to the file.

[@ashokgaddam8996]

Nice

[@H4XOR23]

Is there any way I can schedule teams meeting from excel from tables in spreadsheet?

[@sanketv1]

Filter is an array function , means it will display #SPILL error if list goes beyond available space below it. Is there a way to restrict the list of rows it displays so we will not get #SPILL error

[@ExcelCampus]

Great question, Zohar! Yes, we can use the TAKE function to limit the number of rows. =TAKE(FILTER(), 2) That formula will return the first 2 rows from the results of FILTER. You can specify a negative number if you want to return the last rows from the bottom instead. I'm planning a followup video on advanced uses for FILTER and I'll include this example. I hope that helps. Thanks again and have a nice day! 🙂

[@redhaakhund1271]

👍👍👍👍👍🙏