Google Sheets and Excel - A Better Dynamic Search Bar

[@EamonnCottrell]

⭐⭐Grab the FREE demo sheet here: www.gotsheet.xyz/c/a-better-dynamic-search-bar-in-google-sheets

[@thailiveaboard]

Dear Eamonn! Please Help! I am stuck! Are you provide some service to create tailor made search? I am desperately need your Help!!!! I did for my table =if(ISBLANK(C2),"",SORT(filter(Patient,SEARCH(C2,INDIRECT(C1))),3,FALSE)) and it didnt work! Can I ask for some hand? IDK how to contact to you or your team!

[@EamonnCottrell]

@@thailiveaboardsure thing. share your sheet so I can take a look-(my full name at gmail)

[@archiefrench3124]

Been looking all over KZbin just to find a tutorial like this! Done! Subscribed!🙂

[@EamonnCottrell]

Fantastic! Glad to help!

[@muthulakshmin1147]

Thank you so much @Eamonncottrell. Glad you resolved my queries with Dynamic search and fixed my data! Much appreciate all your help!!

[@EamonnCottrell]

You're welcome! Glad to help 👍👍

[@OrganicPickups]

This is great! Question for you: Can the search be composed of multiple words and to include all columns? I would like to do this so the search would return, let say, a client that meets all of the criteria. Thank you!

[@EamonnCottrell]

Oh, that's a good one. There's probably a better way to do this, but you could filter the filtered results however many times you wanted. Check out this page on the sheet. If you have a sheet you can share with me, I can take a stab at it for your use-case. docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit?gid=290849850#gid=290849850

[@OrganicPickups]

@@EamonnCottrell Thank you so much

[@MathieuLavoie-bt7xg]

Thanks! Any way you could return only some colums instead of all of them with this formula? Say I only wanted Place and Amount?

[@EamonnCottrell]

Cool idea, and yes. It would look like this: =if(ISBLANK(C3),"",SORT(filter({place,amount},SEARCH(C3,INDIRECT(C2))),3,FALSE)) I added this in the sample sheet on the SEARCH tab. You just have to add an array as the first argument in the filter and then make sure each piece (in my case the named ranges, "place" and "amount" are of the same size. docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit#gid=1019782817

[@beachuasyson1049]

Just what i needed! Thank you! Is there a way that the search will only return the last 10 entries (sorted by date, latest date first?)

[@EamonnCottrell]

You could sort by date with the filter. But if you want to limit the return number of entries, I'd use the Query function instead. I did another search bar video using it over here: kzbin.info/www/bejne/a6nKqKmloKmbipI

[@KevinPedraza-b7y]

These are excellent tutorials. I have a question: How did you create the drop down menu for search columns to match the column names in your transaction named ranges? Also, it appears I need access to get the google sheet in this video. Can I get access by any chance?

[@EamonnCottrell]

Hi there, that drop down search column is a bit of over-engineering probably. I created named ranges (place and category) that were those two columns on the data tab. Then in the formula for the search results, I used the INDIRECT function pointed at that drop down cell. Indirect returns a cell reference (the column of our data to search) referred to by a string (the named range). You can make a copy to edit by selecting File - make a copy. Hope that helps! Thanks!

[@derpydragon7056]

This is fantastic, thank you so much for this Eamonn! I've tried to integrate it into my product data sheet and while it appears to be working, there's some odd behaviour in the first row of results. The first column shows every piece of data at once in the first cell, the second column shows every piece of data in that respective reference column, etc. Strangely, this only affects the first row and the rows below act as intended. Any idea what might cause this?

[@EamonnCottrell]

That is odd. Merged cells don’t play nicely. Do you have any of those in your data? Happy to take a look if you want to share a copy with me. My full name at gmail

[@coghilla]

I had the same issue with my attempt

[@EamonnCottrell]

@@coghilla Any merged cells causing this? Happy to take a look if you want to share a copy with me - my full name at gmail

[@kazutomonishimura9180]

This is so helpful! Thank you so much!

[@EamonnCottrell]

You’re welcome!

[@SeanJuen-jv7rd]

@@EamonnCottrell hi sir. mine is not working

[@EamonnCottrell]

​@@SeanJuen-jv7rd What's the issue with it? Could be a lot of things.

[@nicknoone2620]

Thank you!! This is amazing (and super simple to use!). One question, I'd like to have the sort destination changeable by the user. Ideally, adding another dropdown option that would auto update the formula. Do you think this is possible?

[@EamonnCottrell]

Hey! Yes, you could add a dropdown for the data you want to sort. You'd name those ranges (range1, range2, range3...) and then you'd replace "transactions" with that range in the filter part of the formula. You'd have to add additional search columns in the other dropdown to match up with the different options, so it could get a little hairy, but that's where I'd start. Shoot me an email if you end up needing a second pair of eyes on it (my full name at gmail)

[@TristanThomas-pm5jo]

Great video, I have multiple sheets of data to draw from, is there a way to make it so it looks in all the tabs?

[@EamonnCottrell]

I'd create a new sheet that aggregates all the data into one place. For example, ={Sheet2!A1:B3;Sheet1!A1:B3}. This pulls the data from Sheets 1 and 2 together in the order that they're listed in between the curly braces. Then when all the data is together you can use that single source to search through.

[@TristanThomas-pm5jo]

@@EamonnCottrell That worked great! I appreciate you getting back to me so quickly

[@rizzaicaro5566]

Hi! This is really helpful. But I need your help, do you have tutorial for multiple search range and search term?

[@rizzaicaro5566]

can i share my file with you so you can check it out?

[@EamonnCottrell]

@@rizzaicaro5566 Yeah let me know what you've got in mind - share with my full name at gmail

[@rizzaicaro5566]

@@EamonnCottrell Hi, i shared the file with you. :)

[@Zarahatke-k2q]

The tutorial is awesoeme! Is there any video where we can create a google sheets with importrange and a search bar where a user can search his name and get all details displayed in columns against his name? while others cant view any details without entering any name visibile in sheet

[@EamonnCottrell]

Could you clarify a little more? Is the data on a separate Google Sheet and you want to combine this search functionality with IMPORTRANGE? For performance's sake, it would be better to import the whole range onto a sheet of the spreadsheet you'll use for the search functionality. That way ImportRange isn't running over and over every time you change the search term. Then you could hide that sheet if you didn't want it displayed. I have run into issues with IMPORTRANGE when I use it a lot.

[@Zarahatke-k2q]

@@EamonnCottrell got it true its hard that sometimes importrange doest works to I had an issue earlier with 12k entries it failed , My query is let say that we are creating a google sheet and we want users to search using a search box in sheet without being getting access to the data in sheet of any other users.EG there are 5k of entries with details like Name RefId City Approval Date A user simply opens the sheet it might be an xls or google sheet and he wants to know status of his approval he enters his refid in search box created and the data against it is ONLY displayed in this way no other user can view the entire sheet details or the data just his/her approval status

[@fernandohansel4718]

Thank you soo much, you don't have idea of how you helped me

[@EamonnCottrell]

Great! Glad it was helpful!

[@BcSteve]

Great video. Is there a way to get it to search based on position, for example if you put "K" in the search field it returns every word the has a "K" in it. How would I make it so it only searches for "K" being the first letter of the word? Does that make sense?

[@EamonnCottrell]

Ooo, great question. I may have to expand into another video...but yes, to do what you need, you can replace the SEARCH function with a REGEXMATCH function like this (and I made a new tab in the linked demo sheet for you to check out): =IF(ISBLANK(C3),"",SORT(FILTER(transactions, REGEXMATCH(INDIRECT(C2), "(?i)^"&C3)),3,FALSE)) docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/copy

[@BcSteve]

@@EamonnCottrell Youre awesome, thank you so much

[@fraubrehmer96]

Hi there, this is fantastic! I was wondering if there was a way to make the data that is searched all sheets in the Google Sheet itself. Is there some way to make the search bar pull information from every sheet without having to type in all of the names of the sheets?

[@EamonnCottrell]

I'd create a new sheet that aggregates all the data into one place. For example, ={Sheet2!A1:B3;Sheet1!A1:B3}. This pulls the data from Sheets 1 and 2 together in the order that they're listed in between the curly braces. Then when all the data is together you can use that single source to search through.

[@mollytener3230]

Thank you for this. Is there any way to make this editable on a Google Site for others to search? I've created the spreadsheet with the dynamic search as you explained. But I want to allow others to search that database (that spreadsheet) through my Google Site. When I load the sheet to sites, the search line isn't editable for people to type. They'd have to open the spreadsheet. I want it all to live within my google site. Does that make sense?

[@EamonnCottrell]

Oooo. That's a good question. I found that when inserting the Google Sheet to Google Sites, it will not allow editing even if you have permissions on the Google Sheet so anyone can edit it. However, if you instead select Insert - Embed and then Embed code, you can paste in an embedable with the link to your sheet and it will be editable like you wanted. Just make sure that you adjust the permissions on the Sheet to allow for either anyone with the link to edit or just those people who would be logged in to their google account when accessing the site. Here's that code:

[@mollytener3230]

@@EamonnCottrell thanks for the quick reply! That worked! My question now is how to set the sheet up so that when it's edited through the site, it doesn't change the actual sheet for all. When I edit the sheet through my site - i.e., change the search - it changes it on the sheet for all. If more than one person is trying to search at the same time, that won't work. How can I have one person's search only be viewable to them? I'm including links to other docs within the search so people really just need to be able to search to find those other links - nothing needs to be saved within the search.

[@EamonnCottrell]

@@mollytener3230 Gotcha. So, no, unfortunately when embedding the sheet, it is the same as looking at and editing the actual sheet. There's not a way to use the sheet (that I'm aware of) like a unique database for each user. You'd need to convert the date into another form like an html table and then use a little javascript to do what you're asking. There may be another way, but I don't know how off the top of my head without making it more of a web app than a spreadsheet.

[@mollytener3230]

@@EamonnCottrell yeah, that's way more than I know how to do. This will work! Thanks!

[@Cali_G24]

This is amazing! But how to make it unique per user? Or let 2 or more user use the search bar at the same time without affecting each other’s view?

[@EamonnCottrell]

Unfortunately this method will affect other users views. You could duplicate the search sheet for each user. Or you could use the regular filter views that don’t affect other users.

[@michellearanas5305]

Thank you so muhc! This is so helpful and clean

[@EamonnCottrell]

Glad it was helpful!

[@javedamu]

Fantastic. But how to search only specific value because in this case all the matching or contains value is getting fetched. How to avoid that

[@EamonnCottrell]

If you replace the SEARCH function with EXACT function, this will handle it. I made a new tab in the sample sheet with this example for you: docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/copy

[@rizzaicaro5566]

@@EamonnCottrell omg this is exactly what i was trying to figure out!

[@EamonnCottrell]

@@rizzaicaro5566 Haha! excellent! Glad that's what you were looking for!

[@chesterjaysensoro2030]

Thanks for this! I have a question. Can I do a dynamic search bar that lets me search for a unique character and filter these entries by dates? I've done these through QUERY and FILTER function separately but I'm having a hard time marrying both function :( send helppp

[@EamonnCottrell]

You can do this all with Filter, if you want. Same deal as in my example, search for any unique character or string using the search bar. Then highlight the result range and right click - convert to table. (this is a new feature in sheets that makes this super easy now.) Then click the date column drop down from the header in the table and select filter. Select In Between, and then type in the dates you want to filter between. The only catch is that when you change the search term, you'll have to re click the filter for it to refresh the filtered rows. I just added this to the example sheet in the tab, Filtered Date: docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit?gid=1952837841#gid=1952837841

[@ActiveFamilyWellness-y3m]

How did you do the Column search?

[@EamonnCottrell]

I walk through it starting at 2:51 in the video.

[@richardbrimhall]

Is there a way to do this but with a "growing list" of data. For example, I am importing data from a form via Zapier, so the dataset will have the same columns (headings) but rows will be added after each form is completed.

[@EamonnCottrell]

Yes, I would simply make sure to have the range of the data you are filtering and searching defined so it extends down a long way. So if you currently have data from A1:E1000, just define the range in the formula to like A1:E2000 to give you room for 1000 more rows of data. Let me know if this makes sense and works when you run the zap

[@paoloeduardoluna4048]

Can it be edited after searching, then it will be saved at the main data? Also is it possible to have it all in one tab and do not need a seperate data in another tab.. ty!

[@EamonnCottrell]

No, the results you pull up cannot be edited to change the main data. You would need to copy the search result to a new range, edit it and then copy it into the main data. You could code a more elegant way to do that, but it's beyond the scope of what I was doing here. For getting everything in one tab, I've suggested to others to pull all the tabs together into one "master data" sheet that you can then do your searches on. For instance if you have to ranges of data, you can have them stack on top of each other in a new range using the following syntax: ={Sheet1!A1:C80;Sheet1!G1:I80} Note the semi-colon in between the ranges. If you use just a comma, the ranges will be beside one another instead of stacked on top.

[@MatthewBowman-j9e]

This is absolutely fantastic, I've managed to get this to work, but when I change the search term, it sometimes shows an error, but not always. The error is FILTER has mismatched range sizes. Expected row count: 63, column count: 1. Actual row count: 1, column count: 1. Im not sure what I'm missing

[@EamonnCottrell]

That happens when you aren't referencing the right size range in one of your arguments. Probably it's a named range from that indirect function that doesn't reference the correct range to search through. If you want to share your sheet with me, I can take a look. Troubleshooting sometimes gets lost in translation here (my full name at gmail)

[@LamorindaSoccerClub]

Not working for me. Getting mismatched range error. Is there more that I need to do with the search sheet like add a named range, sort, etc.?

[@EamonnCottrell]

I expect it is either because of the named ranges I'm using for transactions, category, and place or because one of your ranges in the filter is not the same size as the other. I'm referencing those by named range in the formula. You can either replace those named ranges with the actual ranges where your data live or make sure that you have created named ranges. For filter to work, though, the ranges have to be the same size (same number of rows). This is where I've made a mistake many times. Double check that too.

[@georgestoll3653]

Is there any way to retain the formatting of the results? I'm using this for a listing of videos with links, and it wipes out the links and any text formatting. Thanks!

[@EamonnCottrell]

I made a sample tab called url formatting to test this. In my sample, it did pull the hyperlinks initially. However, if they are removed from the search result cell, they seem to go away for future results in that cell. The solution would be to copy and paste the entire data set (or at least the hyperlink column) into the range where your search results are going to go. Then delete all the values. This will set the formatting correctly of those hyperlink cells, and when they are populated by the search results you should be good. docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit#gid=1016375007/copy

[@NataliaJohan]

Hi! I was able to make it work with my project. However, I found a problem when searching for numeric data. For example, when looking for number 3, it brings up all the numbers of the list that have that number. For word-based data is really helpful, but makes it hard with numeric data. Is there a way I can fix this?

[@EamonnCottrell]

Hey Natalia, Are you able to share an example sheet with me where you're running into the problem. I'm trying to recreate, but not doing something right because I'm still getting the results I expect if I enter a number instead of just plaintext. my full name at gmail if you can share your sheet.

[@NataliaJohan]

Thanks for the help! I decided to create a second search bar only for the numeric columns and changed "SEARCH" to "EXACT" in the formula as suggested. Thanks again for the help!😄

[@EamonnCottrell]

@@NataliaJohan Excellent! Happy to help!

[@ChandaKalizinje]

So how do we navigate the formular without the FILTER function (older versions of excel)

[@EamonnCottrell]

Hmmm. that's a tough question. I am not sure there's a good/easy way around that for this type of operation. I recommend using Google Sheets if possible for the workbook and/or updating Excel. I expect it is probably your organizations' excel, though, if it's an older version so that may be out of your control; i'm sorry!

[@ChandaKalizinje]

@@EamonnCottrell haha Woops!

[@muthulakshmin1147]

Hi, I created the name ranges but am unable to add the name ranges into a list view, and am unable to use the search on different columns. could you assist on this? I get the error FILTER mismatched range sizes. Expected row count:1000, column count 1. Actual row count 987, column count 1.

[@EamonnCottrell]

I run into this one a lot. It's when the range being filtered isn't the same size as the range of the condition. So one of those named ranges goes through 1000 rows and the other 987. If you adjust one it should work. Share with me if you still run into trouble and I can take a look. (my fullname at gmail)

[@Md_Shahroz_Alam]

if I add anything to the data and add more rows to the data table "transactions" the output shows an error saying "FILTER has mismatched range sizes. Expected row count: 223. column count: 1. Actual row count: 222, column count: 1.". Please help

[@EamonnCottrell]

I've gotten this one a lot. The size of the range you're using for the filter range is one row off from the range you're using in the search indirect part. Just adjust the named range that the indirect function is referencing so it has the same number of rows (223) as the filter range.

[@ahmedalkuraimi]

Thanks for the video; you're an amazing person ❤. Is it possible to save any changes in the correct row? Here is my problem: When I create a search bar, it returns the following columns based on the client's name (Col A = client no, Col B = client name, Col C = invoice no, Col D = invoice date, Col E = item). There is also a column after the item, which is Col F = quantity, but I didn't link it with the search bar. My problem is that when I enter a name in the search bar, for example, "Mohammed," it correctly returns the values of the above columns. However, when I set the number in the quantity column and then clear the search bar, the value I set changes to another client name. Is there any way to solve this problem?

[@EamonnCottrell]

Great question, and what you'll need to do is copy the search results (along with any additional quantities you're adding next to them) to a new range. I would select Copy, Paste Values Only. What's happening is that the cells where the search results appear are simply being repopulated every time the search changes. They won't "stick" in place there, you'll have to move them elsewhere for them to stay put. Let me know if I misinterpreted anything...

[@javierfadrilan5823]

I tried following along the video with my own mini version but got stuck in this part: =FILTER(Table1[#All],SEARCH(D3,INDIRECT(C3))) as it outputted #REF! Not sure what went wrong?

[@EamonnCottrell]

I just emailed you the details. Excel treats things a little differently so I made a couple edits on the workbook you emailed me. The main ones were wrapping the search function with ISNUMBER and then making sure the named ranges exactly match the selections for the indirect function

[@javierfadrilan5823]

@@EamonnCottrell I saw your email! It really helped, I sent another email applying your edits and with some more edits of my own. I was also thinking of adding another search bar but the same filters so it would filter out the results even more. Do you think this is possible?

[@EamonnCottrell]

@@javierfadrilan5823 Yes, you can filter the filter results further. I put a simple example of this in the demo sheet on the search tab where it filters the filtered results further according to payment type = "ATM": docs.google.com/spreadsheets/d/1pYtVD5DlF7n8mUPgqEGYQoGx8jY6UskM59_o7lI8_4o/edit?gid=1019782817#gid=1019782817

[@StealthFly]

I'm playing with the spreadsheet. kind of reverse engineering it, there doesn't seem to be a way to add amounts. every amount is deducted from the total. is there a way to make one of the 'Categories' add to the total instead of deduct?

[@EamonnCottrell]

Oh yeah, I had copied those values from another sheet so the formula to do that broke and I never realized it since I was just focused on the search bar here...A simple fix for one category would be this and I've updated the demo to include it: =IF(D4="Paycheck",F3+C4,F3-C4) A more comprehensive answer is over here on the finance tracker sheet that I made: kzbin.info/www/bejne/fpWWpJWZhceDp7M

[@StealthFly]

@@EamonnCottrell Oh nice, that looks way more simple than the line that was there. Easier to understand and reverse engineer lol. Thank You

[@ce7e]

I don’t know how to make the search column. I did name range but you don’t specify how to put that “filter”. Would you tell me or give me a link? Thanks

[@EamonnCottrell]

The "place" and "category" ranges in C2? That's a dropdown list (Data - Data Validation - create a dropdown list with the options being whatever you've named the ranges you need to search inside). I can take a look at your sheet if I'm still off - shoot me a note (my full name at gmail)

[@TCxjamesFALLEN]

followed it to the letter hell i even put my data into your data sheet and nothing always some stupid error saying rows and coloums dont match. not sure what im doing wrong here hell i even wrtoe the formula out myself thinking the copy paste was doing it and still null...

[@EamonnCottrell]

Strange. It's something very small that's fouling it up, I'm sure. If you want to share your sheet with me, I can take a look. My full name at gmail.

[@MaayanSoudry-j6l]

Hey!! This method worked really nicely the first time I tried it, but then I changed my data a little but and now it's not working If I attach a link to my project can anyone help me fix it? Thanks!

[@EamonnCottrell]

Sure thing. Drop a link or share with me (my full name at gmail)

[@squiddymute]

what if you have multiple sheets?

[@EamonnCottrell]

I will typically create a combined big table on one sheet to make searching it easier. So I'll use array formulas to bring everything together first rather than trying to create a huge formula to search both ranges separately...although you could do that if need be.

[@AmyPedras]

I tried to use this formula but it kept telling me that filter has mismatches range sizes. Tried changing the references but nothing worked.

[@EamonnCottrell]

It's probably off by 1 somewhere. This is what usually happens to me when i get that error. Check out the named ranges list if you're using the same setup as in the video. Data - Named ranges from the menu will open up the sidebar. My three ranges are "category", "place" and "transactions" and they all go from row 3: row 224. If you keep hitting a block, I'm happy to take a look. You can share your sheet with me - my full name at gmail.

[@SBPromoVids]

Tried putting formula in an empty cell and it returned data in cells below... I need help with this

[@SBPromoVids]

Array result was not expanded because it will overwrite data in c5

[@EamonnCottrell]

​@@SBPromoVidsThis means there's not enough empty cells below where you're putting the formula. I've run into this a lot. Either clear out the cells below it so the full results can display or put the formula in a new location (maybe a new sheet). That's why in my demo I had it on it's own separate sheet so nothing was underneath it.

[@happybee7916]

i keep getting error saying "FILTER has mismatched range sizes. Expected row count: 243. column count: 1. Actual row count: 1, column count: 1." I don't really know why..

[@EamonnCottrell]

This happens to me all the time. It's because in the range you've selected for the search column isn't the same size as the range your filtering. Check out the ranges or named ranges that you're using for the filter and the search functions. If you need a hand, share your sheet with me and I can probably spot the problem - my full name at gmail. Thanks!

[@maheshsomasundaram1351]

why duplicate the sheet ? is that how it works ?

[@EamonnCottrell]

It allows you to make a private copy to edit without changing anything on my public demo sheet. 👍

[@Hayatejerry]

nice one sir

[@EamonnCottrell]

thanks! Hope it's helpful!

[@alwaha309]

Great 👍

[@victoriaswift8822]

I'm so close but so far away from getting this. I really want this to work.

[@EamonnCottrell]

You can do it. Share your sheet with me if you're hitting a roadblock on something and I'll take a look. My full name at gmail

[@SeanJuen-jv7rd]

mine is not working. why?

[@EamonnCottrell]

​ @SeanJuen-jv7rd What's the issue with it? Could be a lot of things.

[@SeanJuen-jv7rd]

@@EamonnCottrell can i share with you my sheets?

[@SeanJuen-jv7rd]

@@EamonnCottrell i dunno. it wont search the items I want to be searched. i can share with you the sheets file if you want.

[@EamonnCottrell]

@@SeanJuen-jv7rd Yeah share it with me and let me take a look - my full name at gmail

[@SeanJuen-jv7rd]

@@EamonnCottrell shared it with you sir

[@jcuadrado9518]

why is this not working for me? all i get is N/A

[@EamonnCottrell]

Did you make a copy of the sheet to try out? May have to do with the named ranges. I’m using those instead of explicit ranges so if they’re different it’ll throw that error

[@mar0008]

THANK YOU SOO MUCH

[@88ggplane]

Is there a way this can work in view only mode? Right now my clients can download my database and interact with search from the downloaded version. But every time I update the database they have to download it again. Is there a way they can use it in view only mode?

[@EamonnCottrell]

One way to get around this is to leave your current database in view only mode. Then create a new sheet that you can share editing permissions with your clients. In the new sheet, IMPORTRANGE the database onto one sheet. You can protect that sheet so it always has the current updated info, but the clients can't mess it up. Then on a new tab, create the search bar that is editable by them.