Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples

[@LeilaGharani]

I also really like the advanced filter method with the formula as criteria. Really clean! The array formula is great too. Great explanation. Thank you.

[@excelisfun]

You are welcome! As you well know with your great Array Formula Videos, it is amazing what you can do with Excel formulas : ) ... Yes, no doubt, Advanced Filter Formula Criteria is really efficient!

[@dougmphilly]

@@excelisfun this is like listening to sandy koufax & mickey mantle of excel self help videos.

[@excelisfun]

@@dougmphilly LOVE the baseball simile!!!!! Sandy and Mickey sure was fun to watch : )

[@MoskaTalks]

Could not be more timely, this solves a problem we were trying to figure out in the office.

[@excelisfun]

Awesome! Timing & Excel lead to accomplishments : )

[@johnmatta9577]

Oh my heaven...why are you doing this to us Mike😊..unbelievable formula..I guess that would be the most complicated formula you've done; isn't it?

[@omarioxibit]

John Matta . I

[@excelisfun]

Not even close to the most complicated. But it does involve Matrix Multiplication so it is complex.

[@nadermounir8228]

Super amazing video using the mmult function. Mike U made me love array formuals !!

[@excelisfun]

Glad you love the array formulas : ) : )

[@MWRent]

Brilliant! The first 10 mins was all I needed!

[@excelisfun]

Glad it helps, m w!!!!! When you need help, excelisfun is here to guide and have fun! Thanks for your support with your comment, thumbs up and of course your Sub : )

[@farishaaulina]

Thank you so much for this detailed video! You saved my 2020 life!

[@vladimirgerasimenko7597]

That is the best extraction I have ever seen. Thanks.

[@excelisfun]

You are welcome!

[@michaeldolan7507]

Mike thank you! Just discovered this and it has really helped me with a problem I was trying to solve. Keep up the good work

[@excelisfun3903]

Glad to help MICHAEL!!!

[@isogulaleb]

That's crazy! Exactly what I was looking for! Works like a charm! Thanks a million! :)

[@excelisfun]

Crazy Excel fun, I hope : ) You are welcome a million! Thanks for the support with your comment, Thumbs Up and Sub : )

[@isogulaleb]

One small hitch though: no way to arrange the results? The order of the rows cannot be changed I suppose.

[@ExcelVbaIsFun]

Good stuff! Thanks Mike for the amazing descriptions and keyboard shortcuts. You da man!

[@excelisfun]

No, you da man!!!! I don't see you around KZbin anymore, but I think I heard you are a star at some other site, right? Like Udemy or something? where are you making videos and teaching these days?

[@sesagolu]

Thank you. I found the link. I love all of your videos. I've learned so much.

[@excelisfun]

You are welcome! Thank you for clicking the Thumbs Up!!!

[@Will_Moffett]

Great vid, induced me to buy an Excel is Fun mug in appreciation. In my experience the INDIRECT function doesn't play nice inside the isnumber(search( construct, just an FYI.

[@excelisfun]

Thank you for your support, Will : )

[@1oakdesign353]

Awesome job! Can Excel auto populate if you set up a search page and have data pages with multiple fields. Thank you and feel free to ask for more information if needed. I know this is very minimal information to give an answer.

[@danwest6267]

Sweet!! I was looking for a payee lookup report formula. Excellentee!

[@mohamedchakroun4973]

Waw Mike absolutely great it is unbelivebale what you are doing

[@excelisfun]

Glad you like it : )

[@张小爽]

Love your video!!!!!!

[@excelisfun]

Glad you love the video!!!! Thanks for your support with your comment, thumbs up and your Sub : )

[@louisreadus4519]

I'm working on a workbook with multiple sheets and they're all linked to one master sheet with macros for filtering. It takes extremely long for the mastersheet to filter. Before I linked the sheets the master sheet worked fine because I manually enter data that were in the other sheets. The problem with manually imputing the data is that it's double work. I would love to email you my workbook so you can advise me on better options.

[@J0n0nI]

amazing lesson and great effort thank you so much

[@excelisfun]

You are welcome so much!!

[@eCabinetstipsandtricks]

Mike, Thanks. for the video. It was very helpful

[@excelisfun]

You are welcome. Glad it is helpful : )

[@johnborg6005]

Great!!!! Simply GREAT : )

[@excelisfun]

Thanks John : ) : ) : )

[@johnborg5419]

Beautiful !!!!

[@excelisfun]

Yes!!!!!!!

[@millawitch]

Awesome - I've been trying to make the SEARCH function work with the Office 365 FILTER function - did a test using this MMULT formula technique in the criteria argument and it seems to do the job! Maybe I'm missing something basic, can anyone think of a better solution? Thanks

[@PrincePedia]

Absolutely Great , Thanks Mike : )

[@excelisfun]

Glad you like it : )

[@farhanahmed2042]

G8 work , what if i want the matching records in columns rather than row wise

[@philmanfredi6119]

Awesome - as ever...

[@excelisfun]

Glad you liked it : )

[@MySpreadsheetLab]

My thoughts exactly! Thanks Mike 😎

[@excelisfun]

Thanks, Kevin : )

[@robinmarks6351]

Great Video. but I've got a question that pushes it a bit further. I'm trying to do the same thing you've just done in the video but instead of returning a TRUE or FALSE statement in the "contains?" column, I'd like it to return which one it found. My list is a bit easier as each equivalent of "Job Title" contains exactly one word from the list. Could you help?

[@AmexL]

What if the Job Title column contained cells where there’s multiple titles, or for example, contained two or more of the lookup values such as ‘BI’ and ‘DAX’ and you want your lookup result to include both BI and DAX in the same result cell?

[@ia7meDi]

Thanks mike.. I wonder if I can join the challenges you and mr excel do? it seems i could have some fun too

[@excelisfun]

Mr Excel and I have not done Duels in a few years. Do you make Excel videos?

[@mitzok]

Hi Mike, very helpful video but for some reason I can't make it work properly. I opted for the #3 Solution, but I get wrong answers ... it works in the sense that the LOOKUP formula spits out the correct values but when I apply the AF it copies also wrong stuff. I search for "URB" texts but I also get results with other texts and also missing some "URB" ones. Any insight ? From what I tested .. the second result it's ignored and the first wrong one is showed instead.

[@masterof]

Thanks Mike. I have a strange chanllenge: I need to merge the Column A ("Part number") into one list, from 3 different tables. Each table have a few other columns, one of the column is "type", another is "status". To extract the data from a table which meet 2 conditions (For example, "type" to be "ABC", and "status" is not "End of Sale" (can be any other text but "End of Sale"). In the INDEX command, to get the ROW number that fits the conditions, I tried to use "AND" to two arrays, but AND can only delivery one result, rather than an array of results. I tried to use * to join the two arrays, but couldn't work into ROW(). Is there another formula function can be used?

[@sharkseye9]

i have an issue...this would seem to work but my issue is the cell field data has numbers, text, double spaces, dates, commas, and sometimes forward slashes...I need to extract names of vendors from that data that is not lined up and sometimes the vendor name is repeated. I think this may be a job for Superman!

[@jeanyveslaval4293]

thank you for this video Mike, would it be effective over a dataset of 80000rows

[@excelisfun]

You are welcome. The Advanced Filter Method is THE best way to go for a data set that big.

[@jegane]

hi mike, @7:03 what if we only want to conditionally format cells? can't we use array formula inside conditional formatting? (don't want to extract, just highlight)(@13:53)

[@excelisfun]

Same formula as Advanced Filter, but to format whole row formula would be with a Mixed Cell Reference: =LOOKUP(2^15,SEARCH($K$11:$K$17,$F11)) AND MAKING sure that the Active Cell when highlighting table is in the first row.

[@naresh1010ful]

third method of array formula is a complete bouncer. not sure what's going on with MMult function

[@imsaurabhgupta]

Not able to download Excel - Please share Link of this Excel "Excel Magic Trick 1382: Extract Records With Multiple Contains (Partial Text) Criteria: 4 Examples"

[@rebeexpo]

Hey Mike , for the magick tricks where would you advise to start ? number 1 ?

[@excelisfun]

No, the Magic Tricks are random topics. I have two classes that I teach at Highline College that cover almost all topics in Excel in logical order. #1 Excel Basics: kzbin.info/aero/PL3FBEE51974F03CCF #2: Comprehensive Excel Class: kzbin.info/aero/PLrRPvpgDmw0lcTfXZV1AYEkeslJJcWNKw I have other class too, like Statistics...

[@alexj8117]

How about a Power Query technique for this?

[@excelisfun]

It is much more complicated. Unless you have data more than a million rows, Advanced Filter is the way to go. Bill Szysz has two great methods, hopefully he will make a video : )

[@planxlsm]

8:55 11:09 13:54 - Filter 17:52 - SEARCH ( TRANSPOSE 21:24 - {1, 0, 0; 0, 0, 0 ;...] MMULT

[@excelisfun]

So, finally, after all your posts... now I understand what you are doing: you are leaving a time map for the topics you might lookup later? Is that correct, PLAN XLSM?

[@excelisfun]

This is another good one with good comparisons... : )

[@planxlsm]

@@excelisfun yes, Mike !

[@BillSzysz1]

Awsome!! AF rules :-)))

[@excelisfun]

I sooooooo agree: Advanced Filter Rules : )

[@excelisfun]

Posted above: PQ is much more complicated. Unless you have data more than a million rows, Advanced Filter is the way to go. Bill Szysz has two great methods, hopefully he will make a video : )

[@sesagolu]

I can't find the link for Magic Trick 1382. Is it the People.highline.edu file? I looked at that but couldn't find anything. Please help.

[@excelisfun]

Click link below video, then use Find Feature Ctrl + F, and find "1382", this will jump you right to the file. Thank you for the Thumbs Up.

[@mwachambigeorge7728]

For a person with the most basic knowledge of excel, where do I start from?

[@excelisfun]

Here is my starter Excel class: kzbin.info/aero/PL3FBEE51974F03CCF

[@mwachambigeorge7728]

ExcelIsFun thanks

[@nelsonyap1699]

video not clear