Google Sheets - Intelligent Search to Filter Data

[@shaikan0]

You channel rocks brother! Thank you for all the effort you out into this. You're literally helping people improve their careers if they take the time to watch and learn.

[@ExcelGoogleSheets]

I appreciate that!

[@thehawk40375]

@@ExcelGoogleSheets please share practice file

[@Sim_Pole]

@@thehawk40375 all you need to do is to type what's present on the screen - and you'll need to do that to practice using this knowledge anyway

[@minorchejean1221]

@LearnGoogleSpreadsheets That is nice.

[@nattybbogle]

Your channel has elevated my work life immensely please keep doing what you’re doing

[@ExcelGoogleSheets]

It's great to see these kind of comments.

[@tridsonline]

👍🏻 Excellent .. i love the clever solutions, but i greatly appreciate your style of building up to them and showing how you get there. Thank you!

[@ExcelGoogleSheets]

Thank you very much!

[@tomasrampas1230]

This tutorial still rocks. With this, I was able to make search in a way, that it searches text OR a tag that can be selected from a dropbox. And it works!!! I am so happy :)

[@ExcelGoogleSheets]

👍

[@arnellcarmichael635]

Man, this is super Dope! Especially when talking to clients and searching for information on the fly. I am going to utilize this in my google sheets CRM, and this Search function will help tremendously! Thanks, Brother!

[@ViniBevilacqua]

This is ultra high quality content! Congrats dude! Just found your channel and i'm now hooked to it! Thank you so much!

[@ExcelGoogleSheets]

Welcome aboard!

[@natalieforood4989]

This is so helpful and you explain things so clearly and methodically! Thank you!

[@ahamedjameel8480]

You guys are the real heroes for me. Clear magicians. Thanks a lot guys.

[@ahamedjameel8480]

If you have a patrean account, I'd definitely want to contribute.

[@ExcelGoogleSheets]

www.patreon.com/chicagocomputerclasses Thank You!

[@delphinelouise6403]

Wow! I am so impressed with your work! Wonderful!

[@rontrejos4454]

Awesome the simple way you make it, I actually did it for a work task I had for months sitting there, keep it up!!!

[@sanpatel2056]

God bless you dear for helping & sharing knowledge

[@sharitambasco9780]

Awesome as always. I like it when you take a second to explain all your little tricks, like you did with cut vs paste to update the reference. Thanks for posting, I check everyday :)

[@ExcelGoogleSheets]

Glad you like it and thanks for watching!

[@DavidGomez-le7if]

feww, that took a lot of digging, I was searching how to make this exact kind of search box, I did find others that linked other sheets, and some that just use the simplest method, "search-box" however this is exactly what I was looking for thank you very much, I will keep watching your videos to learn more,

[@rcronin1]

this is a whole class :) Thank you!

[@homerovelasco9892]

Thanks for the video! It is super helpful. I have one question. Can I get search results for the whole spreadsheet if it has more than 1 tab. How can I set up the search results to include results from all tabs on the sheet?

[@silvestrecamposano6317]

Thank you for all the techniques...

[@1burkygayaban199]

Hi! How do you search in multiple columns example on your video you are searching in column B. How about adding additional column like searching in column B, C, and D?

[@rawriding_sm]

Please put more videos about data studio and related new data handling softwares and applications 🙏🏻🙏🏻🙏🏻🙏🏻🙏🏻

[@malbi12345]

Your videos always inspired me 💕

[@gonzalomolina3220]

Fantastic and useful presentation! Thank you. Question (maybe already answered): is it possible to make the filter "live" without pressing ENTER each time to filter it again?

[@memory_null]

Now its just missing sort by closest match but that's easy enough. Awesome content as always!

[@ExcelGoogleSheets]

SORT would make it better.

[@patrickschardt7724]

Wow this is incredible. I would love to a follow up video showcasing an Excel LAMBDA/ LET solution

[@ExcelGoogleSheets]

Thanks! I have LET function video on the channel.

[@SRDFTM]

LOVE your videos this is pure art!

[@ExcelGoogleSheets]

Thank You!

[@snake1987666]

your videos are gold. Keep it up guys!

[@ExcelGoogleSheets]

Thanks! Will do!

[@yazhisaiveandan3075]

Bro! Do you know how to filter it and return the result from the vertical to horizontal position?

[@niranjankumarcoimbatore5842]

Hi very nice video.....Where are you from...I am from South India

[@dongohuy7066]

Thanks so much for your inspiration. That helps me a lot in my daily job. Keep it up!!!

[@ExcelGoogleSheets]

Happy to hear that!

[@waynetrieu1069]

Thank for your work! But is it possible to edit one cell directly in search sheet and it will automatically update the data in the main sheet?

[@mahesasanggadipa]

thank you dude, you solve my problem auto subscribe for you

[@ExcelGoogleSheets]

👍

[@kylaisidro]

how can i show all data before the filter function?

[@rawriding_sm]

Awesome work bro I learnt a lot from you .

[@ExcelGoogleSheets]

I appreciate that

[@jtung9718]

I can search engine find all excel sheet ?

[@thomasravnholt7487]

Great inspiration! What if I wants to be able to search in both description and item number, is that possible?

[@ManojGuptahome]

Thanks. That has helped me a lot. My application requires much the same.

[@ExcelGoogleSheets]

:)

[@onurguner380]

Thank you.

[@ExcelGoogleSheets]

Thanks for watching!

[@dr.mohammedmohammed7633]

could we apply those formula on excel without getting an errors

[@ExcelGoogleSheets]

SPLIT function doesn't exist in Excel, so you would need to create it with VBA in order for it to work in Excel.

[@dr.mohammedmohammed7633]

@@ExcelGoogleSheets thanks for your fast reply... one day i will recommended you as reference in my company

[@mutiapratiwi7379]

Hi! thanks for the video, I wonder is it possible to store the data and match score formulas in google drive or clouds and create the search bar in google sheets? I have lot of data and google sheet can't help, I try to use excel but some formula didn't work...

[@giancarloschafloqueguzman9918]

Great vídeo!!

[@ufuksakar6080]

Congratulations. You video great thanks from turkey 👏👏👏👏

[@ExcelGoogleSheets]

Thanks

[@reverdiefarmgoods]

Can you share a version of this sheet?

[@dr.mohammedmohammed7633]

that was such great tutorial, you simply build a google search machine inside google sheets , kkk kkk

[@steven8293]

Is it possible to make it an arrayformula to match for an entire column (so that I don't have to pre-drag the formula)

[@ExcelGoogleSheets]

It should be possible to create a custom function, otherwise unlikely.

[@davidfernandezbajo]

@@ExcelGoogleSheets actually this works, in cell I2, if out of scope for this channel, maybe on get __it done? ;) =ArrayFormula(MMULT(N(REGEXMATCH(B2:B,SPLIT(TRIM('Search Here'!$B$2)," "))*1),SEQUENCE(COLUMNS(SPLIT(TRIM('Search Here'!$B$2)," ")),1,1,0))/COLUMNS(SPLIT(TRIM('Search Here'!$B$2)," ")))

[@ExcelGoogleSheets]

Looks impressive! If you want to do a video on it I'll gladly post on the channel.

[@steven8293]

@@davidfernandezbajo Great Can't wait to try it out. But yah. probably do a video on it as OP suggested : ) Awesome!

[@stevenwijaya4819]

not lower text

[@lpanebr]

This is great. I love it! I got confused as to why you added the division inside the arrayformula.. would it work outside also? Thanks!

[@ExcelGoogleSheets]

I always use ARRAYFORMULA outside. Just my preference, not a requirement.

[@DavidGomez-le7if]

I only have one issue whenever I input additional data in the range the search box will not populate this result., any suggestions?

[@minorchejean1221]

Thank You for making this Nice Searchbox I love it Keep it Up. I subscribed.

[@ExcelGoogleSheets]

😀

[@mohamed.montaser]

how to get this dataset

[@yashavantpatel8771]

How can I get this training worksheet?

[@samuelanane-kyei4822]

Interesting!

[@ExcelGoogleSheets]

Thanks!

[@ivansanchez1386]

hello, great video, just one question , it is possible to do the search in all sheets with the same filter ?

[@glitchedcitzn]

Yes, it is possible. I watched the other video talking just about the Filter function. Here is an example =FILTER({rang1;range2},{conditionA1;conditionB1},{conditionA2;conditionB2}). Also make sure that you have the same exact number of rows created in each sheet. Hope this helps.

[@user-lm8dd5li8b]

Hello! I appreciate your work on KZbin videos. It helps alot. Thank you! I'm from Russia, Saint-Petersburg, and I had an idea about sharing your knowledge with russian-speaking people. Would you mind if I translate your videos and upload them to our youtube channel? With links on your channel / videos of course. Any way, thanks again, you are making the working process with google sheets a lot easier! Hugs from Saint-P Sorry, i'm asking you here, i sent it on email but didn't get respose so there's my another try here 💚

[@ExcelGoogleSheets]

The same video uploaded to KZbin generally becomes an issues with KZbin copyright algorithm. Not sure this is a good idea.

[@elricho72]

Great Video

[@ExcelGoogleSheets]

Glad you enjoyed it

[@rizaqnashrulhaq169]

THANK

[@delphinelouise6403]

Quick question: Can I create such a search filter with people's names? We deal with recurring customers.

[@ExcelGoogleSheets]

I don't see why not.

[@moshebiener4507]

Thanks for this incredible toturial; it works good, but it doesn't seem to work with numbers only with text, can you help me with that???

[@ExcelGoogleSheets]

I don't really remember what exactly I did in this tutorial, but you should be able to make it work by wrapping your inputs in TEXT(inputCell,"@") function.

[@davidfernandezbajo]

Very clever!

[@ExcelGoogleSheets]

Thanks!

[@Zehel_Fenris]

is there some smart way to search multiple columns and in multiple sheets? when I try to make an array of filters I get an array_literal error and when I search on more than one column I end up with matches of 200%+

[@moshebiener4507]

Yes, you can use the or operator "+" instead of the OR() function which doesn't work with FILTER() function. See an example here >> =IFERROR(IF(B2="",ARRAYFORMULA(Settings!D5:CE1899),FILTER(Settings!D6:CE1899,(Settings!B6:B1899>=50%)+(Settings!A6:A1899>=50%))),"No matches found.")

[@philchua3049]

You rock dude!

[@ExcelGoogleSheets]

Thanks!

[@novic84]

❤❤❤ u!

[@bruno_veloso]

Awesome.

[@ExcelGoogleSheets]

Thank you! Cheers!

[@dylanmeyers4436]

Awesome!

[@ExcelGoogleSheets]

Thanks!

[@nationallumber5843]

For some reason COUNTA is giving me a wrong answer. On a string of 6 words it returns an answer of 2. So I can't get this to work.

[@ExcelGoogleSheets]

Check the results of SPLIT function and see what it gets you.

[@dianlaksmanaanindita9576]

Hi please make a similar system but for looking some phrases in google docs, can you do it?

[@ExcelGoogleSheets]

Google Drive search doesn't work for you?

[@dianlaksmanaanindita9576]

@@ExcelGoogleSheets i means from sets of google docs files stored in google drive, eg. speech files, and then we would like to find/search keywords, eg: climate change in those sets of files....can we do that?

[@ExcelGoogleSheets]

@@dianlaksmanaanindita9576 Yes, just use google drive search, it already has this functionality;

[@muhammedaadhil5645]

Is it possible to bring it to webApp ?

[@ExcelGoogleSheets]

Sure, why not.

[@rrnn2490]

гений

[@ExcelGoogleSheets]

Thanks!

[@orizzontebiellese]

WTF BRo °_____°

[@DaleEhrhartgisd]

Your videos are fantastic. They're the first thing I look at when I start something including the spreadsheet I reference below. Query question: is it possible to use concatenate in the data source? Something like: =QUERY(concatenate(B3,"!A1:E "),"Select * Where B is not null"). I'm using a drop down to change the data source from a bunch of other sheets in the workbook. It's working fine, but it's not an elegant solution. Here's what's working : =QUERY({Mobility!A1:E; Control_over_Environment!A1:E; Seating_and_Positioning!A1:E; Hearing_and_Listening!A1:E; Study_and_Organizational_Skills!A1:E; Math!A1:E; Environmental_Control_and_Computer_Access!A1:E; Oral_Communication!A1:E; Reading!A1:E; Spelling!A1:E; Written!A1:E; ADL!A1:E; Vision!A1:E; Recreation_and_Leisure!A1:E; Pre_Post_Vocational!A1:E}, "Select Col2,Col3,Col4,Col5 where Col1 ='"&B3&"' ")

[@ExcelGoogleSheets]

Look into this kzbin.info/www/bejne/jKbLaJ-eZ9Kbh80