Fantastic tutorial ... you just saved me and my team a ~huge~ amount of time and effort. Thanks for taking the time to produce and share this tutorial.
Love it how implanted the split them join to create such a loop! Amazing idea, more to it I've adjusted the AND to OR too show all data in single words, thanks a lot, gonna be watching more and more of your vids to learm more too, never thought SQL could be such fun and amazing language and excitingly challenging to use to help me out big time with my needs 👍🏻👍🏻👍🏻👍🏻👍🏻
@Ömer Faruk Akkaya @21:12 see the AND after JOIN! ... change the AND to be OR, so by this the query will turn the results to anything that contains any of the words/letters you put in your search criteria. if you put DRESS BABY it will show you any results containing ANY of these two words and not necessarily containing both, hope it cleared for you ;)
Absolutely brilliant tutorial my guy! Very easy to follow, and educational! Thanks for putting this video together, and it definitely helped me save time searching data within my CRM! Best - Arnell
to make it search for multi columns: _=QUERY(data,"SELECT * WHERE D LIKE ""%"&B2&"%"" AND (C LIKE ""%"&B3&"%"" OR B LIKE ""%"&B3&"%"") ",1)_ notice that AND & OR are logic gates and you should change between them if there is no result
Thank you for the tutorial! Easy to follow, but I also learned some functions of formulas as well! We created a searchable database of flavors and nicotine strengths for our vape store with this. You've given my workers a nice cheat sheet for sure! Thanks again! 🙂
Great video helped alot with me searching but I have a question how can I do this search to entire workbook or multiple worksheets? Can you please help?
This is a great tool and you explained it very well! Thank you!! I just have a question. Is there a way for the search results to be blank when the search drop-down is empty?
thank you so much for sharing. I just want to ask to you if what is your advise to control the simultaneous flashing of result from the query using the QUERY ONE function when the google sheet users had type their own searching data in the search box at the same time? Thank you very much
Hey there, can i ask question? About your video? Is it possible that if i can and search function on specific sheet it can able to update the data also ?
I did this tutorial and it's amazingly help me completed my final task, Thanks! But I've no idea how to create this when I've few different data in multiple sheets, looking for it
Very useful class. You taught how to use query function with examples of region and reps. Can we have search option along with regions and sales reps. 🤔
This is awesome, just started using this. Wondering how to handle B2 being empty - split function doesn't like it. I'm trying to include ALL results in the query if B2 is blank?
Thank You Sir from Cambodia and this help me a lot. would u mind create Query related to Bar Chart report or Graph Analysis in the next video? Thank You.
Thank you! Is there a way to lock the search box so that when one person enters a search it does not filter down the data for other users looking at the sheet?
Hello, So I want to do a thing, when I search for example: an apple, it will check all the lists and when it finds the word apple, it will show it under the search bar (like in the video, but it will check all lists). Is that possible? Can you help me?
Hi please badly needed help. I want to add cell where i can search for another column like C4 will search in column D, D2 will search in column H etc. How can i add in this function? Thank you
Thank you so much!!! you help us to work much easier ! May I ask if query function can also import both value and format? seem like it only import value not include format of that cell.
It only imports the value. You can specify what format you want for the output, but it's not going to pick up the original formatting. Watch my QUERY function series for more details.
Hello there. I have a long log of footage in spreadsheets. The scene descriptions are in cells that contain a significant amount of text and when I search for a word, I am always shown the whole cell, but not the word itself, which in my case is a bit of a problem. Is there a way to get a search result that is more detailed than just the cell where the particular text string is? This would be very helpful, thanks!
Can you Please add UNIQUE (or something) so that my Hyperlinks are returned intact and still function as hyperlinks? Queary returns them as useless data. Thanks for the great tutorials
Thank you for this... For some reason the formula isn't working on numbers though? I have a column with numbers and fractions to search but it only give me the header when I search with this code. Is there anything I need to add to get this to sync with numbers and fractions?
Could anyone can share a code about searching not just only 1 column (E), but 2 or more any columns? In this instance, I want to search the name of a person too. Thank you.
Just wonder for like i want to search by either description of products or the code of the product. How do i modify the query? Because for the description of the products work fine for me. But I want to improve it by search by the code. Really appreciate for the answers.
@@ExcelGoogleSheets I have the same question. For example: the code for the product is something like this: “12345abc678de” with no space between it. But I only want to type just the first 5 or 8 letters in the search cell and the query will show the results of all the closest ones that match the first 5 or 8 letters from the code of the product. Thank you so much.
I have noticed that if we put value that contains a "-" then that value is not retrieved from the cell. E.g. Range"A1" put value "123456-78945". If i use select statement then this value is not retrieved, however, If use 123456789459887654 (a longer value) then its retrieved. Can you make a video to explain this, please?
thanks for this great video. Is there a way to make the search check different tabs on the form. I want to search data in column A on multiple tabs rather than just one tab. thank you
Both If and Indirect will do the trick (+ filtering of null values), e.g query(Indirect(*here comes drop-down with sheet names*&"!"Range") or QUERY(INDIRECT(A2&"!A2:D"),"select *",1).
🙏 Very helpful seeing how to escape the content apostrophe with the double double-quotes. Is that the only practical way to handle that? In SQL, the content is escaped so that it won’t cause this problem. Is there a way to escape the content in Sheets query?
I wasn't able to find anything. If anyone knows I'm interested too. That being said you could use SUBSTITUTE function to replace double quotes in your data with double double quotes and then it would work like escape.
Learn Google Spreadsheets Thanks. I was thinking of maybe using SUBSTITUTE ‘ for its CHAR equivalent to avoid the problem with content, but I have yet to try it.
HI at timeline 3:06 how do you make this query formula case insensitive? I type a lowercase but the data source are all caps so it did not show anything. when I type all uppercase, then I will have a result
I get the search Result when I enter the words but I keep getting this error when I clear the search box Error Function SPLIT parameter 1 value should be non-empty. please help me!
Hi, Thank you. How if we want to search for multi column? Example: for the Category is "Skirt" and Region is "Midwest". Is it possible to use 2 search box?
Hi, I’ve used the following formula like yours in order for me to achieve what I need. So I have the data from the database sheet where in I get to find things on column D =QUERY(Database!$A:$T,”SELECT A,D,O,P,R,S WHERE 1=1 “&”AND LOWER (D) LIKE LOWER(‘%” &JOIN(“%’) AND LOWER (D) LIKE LOWER(‘%”,SPLIT(B1,” “))&”%’)”,1) Now, May I ask what can I do if I also want to search for data in Column A, those data contain numbers. What formula can I add to the above? Really appreciate every videos! Thank you!
For those who wants to use more than one column to quary use this formula.. =QUERY('Physical Book'!A:D, "SELECT * WHERE LOWER (D) LIKE LOWER (""%"&i22&"%"") AND LOWER (B) LIKE LOWER (""%"&j22&"%"")",3) It's for two different columns. Just change some things according to your sheets.
Is there a way to make a search box that filters as you type? So you don’t have to click out of the cell to see the result. I saw a video that Excel could do that.
Hi Sir, How can i make this query sheet be accessible for multiple users to use it simultaneously without affecting the result of other user,,, please help!
isn't there a way to give a research for a whole list of words at the same time ? instad of cliking on one by one please ( when you clicked on the small arrow to bring the list , can't i just put the list there and aplly the function to everything ? )
Hello, why are my results different from yours? I entered the exact formula and all I get is the first row of the table, not even the same Category in the search bar. Can you help me please
Hi, what formula or function can be used to fetch two letter US state data which is in a column in another tab in the same workbook. However, in that column some states are mentioned in two characters(Short abbreviations) while some is spelled in their full form. I have a third tab which has only two columns. One with fully spelled state names and next columns contain two letter abbreviations. So we need all data in another tab in two characters only. How do I put the formula to say that if in 'State " column there are two characters bring that exact same data in this tab, however if it is more than two characters, go to the third tab and search from the state name and relevant abbreviations, and finally bring back two character state name. Please help
Not exactly the right video for this, is there any way to read or update data using the Google Sheets API using a sort of search query, instead of just specifying a range?
Sir, how can we pull data multiple Times using this commad, but data should displays in next row. e.g, if I use query function to pull data and it returns two rows and next query start displaying data from next row. Please suggest solution
Yup possible =QUERY('Physical Book'!A:D, "SELECT * WHERE LOWER (D) LIKE LOWER (""%"&i22&"%"") AND LOWER (B) LIKE LOWER (""%"&j22&"%"")",3) It's for two different columns. Just change some things according to your sheets.
How would I be able to display results if my search contains quotations? Eg. _____ I want to search for: 3" Screws ______ Result: # VALUE I followed your instruction at 3:54 and it worked for all my searches containing an apostrophe (eg. 9' Pipe), however it will not work with quotations...is there a solution?
