Google Sheets - Search, QUERY function
HTML-код
- Опубликовано: 29 сен 2024
- Learn how to create search box in Google Sheets using QUERY function. We'll create basic search for exact match, then search for text that contains the query and finally create a search that will find matches to multiple words in any order.
#googlesheets #search
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.
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
The Real MVP
This man needs to get pray for his kindness
the function query is good, I like it! and thank you so much for sharing this easy function.
VERY VERY USEFUL VIDEO, THANKS
hi! in this case we can make the same cell to search in more in column A,B ,c...? ( 1 single cell can search by name, phone number or date)
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
Perfect video i have ever seen
Thankyou so much
Exactly what I was looking for! Thanks man
Thanks for clear explanation. You are wonderful tutor.
Really nice for me to get it resolved my issue but same thing how can i do it for multiple sheet to fetch the same result
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?
Yeah. I have the same question. How can you have all the options show when the field is blank?
There's anyway to make the results of the search to carry the text formatting used in the data?
Awesome tutorial.
What would the query be to search multiple tabs/sheets?
Love it! Thanks bro! This is so good!
i have used this and thank you for this video
Great Job!!! Thank you so much!! I wanted to ask you to do something like this....but you had the Idea before I could ask!! Thanks!!!
Thank you so much!
Good job 👍
Really thank you Buddy
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?
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?
ruclips.net/video/nLW8SerwnJo/видео.html
is it possible to limit the result to only certain column instead?
🙏 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.
Where can I find the google sheet so that I can follow along with you?
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!
Anywhere to download a working version that I can make fint my data? I just cant make this work
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
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?
Doubling quotes usually escapes them, So I would try to to wrap the reference in SUBSTITUTE(A1,"""","""""") and see if that works.
I can't solve my problem by watching this video. My problem is the same.
When I use this formula, it shows the error text . Please help sir
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.
You can only do it if you use some sort of web interface, like in this example ruclips.net/p/PLv9Pf9aNgemvM36efLpaHxbkZTGp2pfhx
@@ExcelGoogleSheets Ok cool. Thank you.
AND function is not working for me..
Yeah,...the first half of the Video made sense, the last half you went off on some "Baby Toddler" Tangent and I have NO IDEA what in the hell you're talking about, the closest I can come to it is you are telling us to write a Formula for everything we are Searching for which pretty much defeats the whole purpose of a Search Function :D
#Formula_Phrase_error "Once i try to make it dependent on a cell
It shows my header
This is great! But I have a question; What if I have a column that has percentages like 0%, 10%, 30%, etc.. How can I do a search for all the items within my data set at "30%"? For whatever reason, the parse is not reading the "%" in my columns. It keeps saying "Query completed with an empty output." Can anyone help???
try 0.3 instead of 30%
@@ExcelGoogleSheets - This change did not work. It still says "Query completed with an empty output."
Here is my command =QUERY('Query for Dashboard'!$A$3:$O, "select C where G = '"&D4&"'") and D4 has a Data Validation dropdown of 0% 10% 20% 30% 40% 50% etc....
@@jacquesduval3723 try =QUERY('Query for Dashboard'!$A$3:$O, "select C where G = "&D4)
@@ExcelGoogleSheets this is the error message I get when I try that
Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "G "" at line 1, column 16. Was expecting one of: "(" ... "(" ...
@@jacquesduval3723 Please create and share an example sheet.
Sr, plz share this Google Sheet File
again.... you are "THE" SOLUTION....
I've been doing this search problem for two days now, and finally, after watching your video, I found a solution to my problem.Thank You Sir.
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.
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 ;)
Your video is excellent but can you please share the sample file too so that it gets easier to along ?
=QUERY(data,"SELECT * WHERE 1=1 “&”AND LOWER(E)LIKE LOWER(“”%” &JOIN(“%””) AND LOWER(E)LIKE LOWER(“”%”, SPLIT(B1,””))&”%“”)”,1)
I am using IMPORTHTML but the Query isn't reading the table I pulled in. Is there a workaround?
Use FILTER function
THAAAAAAAAAAAAAAAAAAAAAAAAAAANKS
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
Can I do the search on the whole array instead of just one column?
Why is all of my search result ending up in one row?
Same. Did you figure it out?
@@fvc421 Same thing, please share if you happen to fix it :)
Also, let me get this Straight, so let's put this in some kind of Useful Perspective, if I have a list of Model Numbers for an Inventory of items I have to Write a Formula for Everything in my Stock or the Search Function is not going to find it?
Greetings. I'm looking to query from a dropdown a data set that does not always exactly match the reference in the dropdown. I've tried CONTAINS, LIKE, wildcards (such as %), and it doesn't work. For example, my data would include "Kites Red", "Kites - Red", "Kites Blue", "Kites - Blue", etc. When the dropdown shows "Kites - Red", the "Kites Red" results do not appear. What query syntax can I use that will result in both "Kites - Red" and "Kites Red" showing as search results? Thanks.
I do not know what's wrong but why can't I search two different words. The data doesn't appear. Can someone help me please
Here's the formula I used:
=QUERY(Manhwa!A2:G,"SELECT * WHERE 1=1 "&"AND LOWER(B) LIKE LOWER(""%" &JOIN("%"") AND LOWER(B) LIKE LOWER(""%", SPLIT(B1, " "))&"%"")",1)
=QUERY(MP!A2:O;"SELECT * WHERE LOWER(G) LIKE LOWER(""%"&A1&"%"")",1)
doesnt work, =QUERY(MP!A2:O after "," or ";"?
error : syntatic error on formula
If i have a database with some information, and i work totally on a new sheet. For example, if i write: (A) name, (B) surname, (C) phone, (D) email, how can I get on (E) confirmation that already exists?
How do you have this search box search through 2 different Columns for the same word(s)? Keep the same layout as you see in this video but it will search thought Column D and Column E for "baby Dress toddler" Thanks!
This was exactly what I was looking for to try and put a search function into a spreadsheet I've been working on! Thank you so much for sharing this easy to follow and informative tutorial ♥
this is a very helpful and useful video but i have more question. Is it possible to do Query with Number bc i tried to do the same as you teach but it doesnt work for me . please help
thank you :)
Same here
doesnt work for me :( maybe something with the spaces in the formula (still struggling with the first basic query in the video) any help?
How can I pull an image also, (one of my columns is an item image, rest are the names and numbers). There must be some kind of workaround to get the image aswell when using the searchbar?
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
Data Validation option has changed and now won't let search of multiple words occur. It only yields first option from alphabetical list.
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?
like a 1000 times
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?
I Love You So Much
I tried this but it says "Query completed with an empty output." How am I gonna Fix this :(
Anyone watching this, you have to CLICK the "B" cell, not type it when doing the reference
how do you also pull through the hyperlink in the data sheet as well? So say your skirts has a hyperlink to open up a video and you need this hyperlink brought through so that you could click skirt and watch the video?
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 ?
THANKS! I've been through many of your videos looking for something like this.. Really Helpful
The search function searches all columns, this video only shows searching a single column
Hello sir good day, how can I search for multiple columns?
Great! In a drop-down can you allow multiple selections instead of only one?
why is that my query function in google sheets doesn't work?
Is it possible to not return any value if the search field is empty???
very helpful and informative! thank you so much!!!
HI. I WOULD LIKE FOR A CELL TO SHOW THE NUMBER OF RESULTS FOUND. HOW WOULD I DO THAT?
I tried every other symbol. but once i put comma after =QUERY(data!A1:B10 The "(dt!A1:B10" loses it's orange color and becomes regular text which I guess means its already wrong. I dont get why? Its soo annoying that this very simple thing give a shit right from the beginning.
Wow
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?
Is there a way to have the query function, pull from two or even three different columns in seperate search cells? So example I want to search the Location Column (E column), The Job Column (F Column), the Availability Column (G column), and I want each Search (location, Job, Availability) to be in their own separate search cells as drop down lists. So in Cell A5 is drop down search function for Location, in B5 is Drop down Search Function for Job, and C5 is Drop Down Search for Availability. I want to give the searcher several options on how to find the Artists that fits either one or search parameters.
You can try something like this (where A1=1st dropdown and A2=2nd dropdown). QUERY('Raw Data'!A1:E, "select * Where A is not null "&"IF(A1="AllResults",""," AND Lower(A) = Lower('"&A1&"') ")&IF(A2="AllResults",""," AND Lower(B) = Lower('"&A2&"')"),1,)
Found Simpler solution based on AND Statements =QUERY(Your Data Range,"SELECT * WHERE E LIKE ""%"&A1&"%"" AND F LIKE ""%"&A2&"%"" AND G LIKE ""%"&A3&"%""",1). Also, you can supplement those ANDs with ORs so that you don't get blanks/errors.
@@marcobanderas3306 Hello, can I ask you questions?
At the beginning of the video, I was looking at it and thinking: "Oh...boring, every newbie knows that"... but later, close to the end... the only words in my head were: "What a tricky mo***fu***, u got me!"
Big thanx for that tutorial.
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.
My query does not work because the qualify field is list of names formatted LAST, FIRST Mi., Jr. ----way too much punctuation.....please help
This is spectacular, simply Amazing!!!!!!! Thank you!!!!!
Greetings from Colombia friend
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
what if i want to bring data from other sheet within google sheet
Hello, this was very helpful but I was wondering how to do something. Is there a way to search for things in multiple columns at once?
Sir, please help me to fetch num and text value from single column. Query fn. Fetch num value, but TXT not. Pls help me....
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 ? )
Wow, this is great. A million thanks. Please, supposing you want to generate a cost for two different items, how do you go about??
Can I hire you for a project?
Can someone put up a function using filter function instead of query, so that the returned data can preserve hyperlinks?
Try one of these
ruclips.net/video/Jg-fc8ZRpFc/видео.html
ruclips.net/video/JQSlbQeEz1k/видео.html
Thanks, very helpful. Following most of your tutoring.. Just want to find out why after search result when you edit any data, all search data disappears and the formula reruns errer
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
Tried Your String Multiple times & Its Not Working ! The Search Querry of b1 cell its not coming up for me tried 15 times
Why nobody shared the online results?
why doesn’t it work with date formats?
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
I guess [... WHERE 1=1 " & "AND ...] could be [... WHERE ...]
Absolutely! at least it works. 1=1 looks dirty
Yeah, the mock up text condition must not start with “AND” in the first place.
Also LIKE can be replaced with CONTAINS. Also to make it more verbose, make an option to choose “AND” and “OR”
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.
Your method of teaching makes everything look easy, you're my most important resource of learning google sheets, thank you so much !
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!