Please include in your title that this is for Office 365 only. I just wasted 35 minutes I can't get back only to find what other commenters found out based on your response, that this is only for 365 Users. Kudos to you however for the 365 Office App and this tutorial. You still get a thumbs up from me. Now I need to find out how to do this in MS Office 2019.
Hi Suzukii, Thank you for your constructive feedback & even more thank you for the thumbs up even though the content didn't match your expectations. You are right, it might be a bit misleading that I haven't mentioned Office 365 in the title. I assumed people know if they have the FILTER function available in their version or not. Sorry for that. However, to make up for it and save you some time searching how to do this in MS Office 2019, you might find the last part of my latest video helpful. In there, I have covered how to replicate the functionality of the FILTER function in older versions. That part of the video (starting at 45:04) covers how to implement 'Searchable Drop Down Lists': ua-cam.com/video/JTduguvrF34/v-deo.htmlm04s If you want to directly jump to the part in which I replicate the FILTER function's functionality for older versions, here is the link with the timestamp 49:22: ua-cam.com/video/JTduguvrF34/v-deo.htmlm22s Hope that helps you to implement the search box in your Excel version. I am curious to know, so feel free to give me another quick feedback whether it was helpful here or under the other video. Thanks again and have a great day
Same with me. I don't have the filter function . I am right now using vlookup formula fir this. Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
Great tutorial! Notes to myself, this part 1 tutorial doesn't convert source data into table yet (which part 2 does). So this tutorial works with absolute cells at this point.
Great stuff. Amazing tutorial. The two things I would do differently are: 1. Put the IFS function in the INCLUDE argument since the only thing changing is the search mode formulas. 2. Use SWITCH instead of IFS since your IFS conditions checks the same cell (for different things). And now with the edition of LET and LAMBDA, I’d probably incorporate those too
Sir! Not sure if it will be justified if i say thanks a lot for such life changing learnings.. Hv been using this n all other valuable training in my life and its really very helpful 🙏
Their is a very informative tutorial. I have a question, regarding the partial match function. Is it possible to have nothing displayed in the output table, rather than all data set, prior to input of search text. Thanks
That was exactly what I needed. Thank you very much! Just a question. If I have entries with punctuation. Could it be possible while I type/search, to return results even though I haven't typed the punctuation?
Would it be possible to limit displayed entries to 6rows while using partial match formula? Trying to add this search box into dashboard with limited space. Great stuff btw
Can anyone please tell me how one can search for numbers instead of text? Also can this be used for those how have older excel (365/2019) please? So basically can this be done with VBA instead?
I don't think the FILTER function works on numbers. When searching a number, what I did was convert the original data set column with numbers into text, by using =TEXT(cell,"0"). Hope this helps you!
Can i use checkboxes to search in different sheets if checked? Example: If first box is checked E1 is true and the search should do its thing in the first tabel, if second box is checked E2 is true and it should search in table 2. But if both are checked it should search in both. It should be something general because we can have 5 checkboxes..
I am using this help create schedules for our company. I want the search box in Worksheet A but want to search the staff list from Worksheet B. Is this possible? Every time I try I get errors and it won't allow to me to use the cell from Worksheet as my = to cell.
Hello, thanks for the video! Is there a way we can copy the search results? Example: I use the search function, I get the result and I want to copy the text within a cell result (example, if it’s a link). Thanks!
Great! This is exactly what I was looking for. I do have one question if anyone would like to answer it. How do I get a comment to appear in array cell from the original cell it is referencing? Specifically, lets just say that in the comments were the cities these people lived in. Chris Miller's (A13) comment could be "Pittsburgh" and Leilas (A14) comment was "Melborn", how do I get that information to populate into the array? Specifically, what I am doing is a catalog of parts. In it, I have information about the parts such as material used in construction, weights, applications, measurements... etc. I want these comments to be visible in the array. Thanks!
Really great tutorial. But how do I implement the result in the search area? I acutally want the same function as MS filter, but without the arrows on top of the columns.
We find, that using these kinds of formulas for search boxes dont work on sharepoint. Users who have the workbook open will get sync errors when other users search. Have you encountered this and is there a solution?
This is very helpful working on a bunch of data. Could you please make a tutorial on displaying a report file/excel file/daily report using its date. example..say..November 17, 2020. by using 3 combo box and 1 button that when i click to the button, it will display the file/daily report from a bunch of reports on a folder and when it displays the file it should have also a print button and a back to main button. and could you make it on an older excel version? mine is 2010. lol.
Hi Dell. Unfortunately, Excel 2010 does not have the FILTER function that is used in this tutorial. However, there is a workaround to replicate what the FILTER function does. I explain this workaround in at the end of my latest video in the part covering 'Searchable Drop Down Lists'. Here is the link: ua-cam.com/video/JTduguvrF34/v-deo.html The part about searchable drop down lists starts at 45:07 of that video.
@@EngrWUAV Yeah same problem here. Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
Hey, im using the real-time data search box for work and the filter works, but i got hyperlinks in my data and they dont get shown as a link to the website, is there any known workaround?
The FILTER function might not be rolled out to all Excel users yet. It depends on which updating cycle you have set in your office account settings. To make sure your version is updated to the latest releases, you have to select the 'Insider Program' for the Updating cycles in your Account Settings. Here is a instruction from the official Microsoft Page describing how to do that: insider.office.com/en-us/handbook Hope that helps. Don't hesitate to reach out if you have any further questions 😊
Yeah same problem here. Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
Is it possible to use the search box function to show results with clickable Hyperlinks? - We have a spreadsheet of drawings listed by part number in one column and a second column displaying links to the drawings but using this method doesn't allow the link to be clicked. Any help would be appreciated and thank you for this tutorial, very useful.
Excel is letting me dynamically search all my data which is perfect. However all the criteria in the table array is showing all the time instead of populating when I search for it in the search bar. How can I fix this?
Hey there, I am having some trouble. The functions are working great, except when the cell is blank I am seeing the whole list. It does not use the "NO MATCH FOUND" that we entered in the [if blank] criteria. Any ideas why? How do I fix this?
Hi Stephanie, I guess you are talking about one of the partial match variants, correct?! For these variants, if you have a blank search cell, the FILTER function searches for an empty string expression (""), which technically is part of every word. That's why the whole list is displayed. To fix this, I you have to expand the 'include' expression with an IF statement that checks if the search cell is blank, like this: =FILTER(array;IF(ISBLANK(search_cell);FALSE;ISNUMBER(SEARCH(search_cell;search_array)));"No Match") Hope that solves your issue 😊
@@theofficelab I used the Formula =FILTER(A9:C21,IF(ISBLANK(F3),FALSE,ISNUMBER(SEARCH(F3,B9:B21))),"NO MATCH FOUND") to test this out on the "Partial Match" area of the "Search Box" tab from the downloaded tutorial worksheet but it still displays all lines when the search box is empty what is wrong?
@@theofficelab Hello, Im having issues with this and believe I figured out why... but unsure how to fix. Basically, if I do a =ISBLANK(search_cell) it returns FALSE even though the Textbox is "Cleared" (Nothing inputted). If I doubleclick into the search_cell and hit enter the =ISBLANK(search_cell) returns TRUE. I have not been able to figure out how to fix this though. I am hoping that the formula will see the search_cell as blank when the Textbox is blank Any help would be appreciated! Thanks.
Please I want a help In Excel if I want to update a cell (Old number +New number) in real-time updation Means going on adding new number to old number (existing).. How can I do this .? Please help if anybody knows ...
Hey man, thanks for the tutorial! Although I'm having some trouble with the Partial Search. I'm getting an VALUE ERROR, does anybody knows what could be wrong?
will this work on excel 2019? (NOT 365 - no way i'd pay rent-for-life software lol). Also can you search for words/letters contained on your spreadsheet (either in entire sheet, or in the selected cells/columns/rows) and have your real-time results fill in the WHOLE page as you enter letters in the search box)?? - My sheets have many rows & columns so I need to see the WHOLE page/sheet while my results are narrowing down as I type. The little boxes you show are really not convenient for my use as they can show only very little. THANKS! (BTW I'm on a Mac - no more Windows for me since 2007...) :)
IT JUST DOESN'T WORK..... I THINK YOU SHOULD DO IT FROM THE BEGIINNING, LIKE WHEN CREATING THE TABLE, AND THOSE BOXES WHERE DATA FILTERED... HONESTLY, EVERY SINGLE TOTORIAL I HAVE SEEN, I'VE DONE EXACTLY WHAT VIDEO SAYS, BUT STILL NO LUCK... PLEASE SHOW US FROM THE VERY START.
![Real-Time Multi-Column Data Search Box in Excel with FILTER function [Part 2]](http://i.ytimg.com/vi/O2V1YxtRvRg/mqdefault.jpg)
![Real-Time Multi-Column Data Search Box in Excel with FILTER function [Part 2]](/img/tr.png)
Click here for Part 2 of this tutorial: ua-cam.com/video/O2V1YxtRvRg/v-deo.html
Glad I found you!! Thank you, thank you, thank you for this tutorial!! I have been searching for something like this for two days!!!
Thank you so much!!! Great tutorial!!!
Easily some of the best excel tutorials out there.
Wow, thanks! 😊 Glad you think so
pro tip: you can watch series at flixzone. Me and my gf have been using them for watching lots of of movies these days.
@Russell Vicente Yup, have been using Flixzone} for since december myself :)
This is the best and complete tutorial I have ever seen, thanks.
This video was just too perfect and detailed! Well done Sir! Just what I had needed!
Thank you! Great to hear that.
Well-Done Man, thanks for sharing the wonderful Ms Excel tutorial.
Thanks Aimal, glad you enjoyed it! 😊
Wow!.... The best explanation... Thank you very much for the knowledge gained.... More power..
Please include in your title that this is for Office 365 only. I just wasted 35 minutes I can't get back only to find what other commenters found out based on your response, that this is only for 365 Users. Kudos to you however for the 365 Office App and this tutorial. You still get a thumbs up from me. Now I need to find out how to do this in MS Office 2019.
Hi Suzukii,
Thank you for your constructive feedback & even more thank you for the thumbs up even though the content didn't match your expectations. You are right, it might be a bit misleading that I haven't mentioned Office 365 in the title. I assumed people know if they have the FILTER function available in their version or not. Sorry for that.
However, to make up for it and save you some time searching how to do this in MS Office 2019, you might find the last part of my latest video helpful. In there, I have covered how to replicate the functionality of the FILTER function in older versions. That part of the video (starting at 45:04) covers how to implement 'Searchable Drop Down Lists': ua-cam.com/video/JTduguvrF34/v-deo.htmlm04s
If you want to directly jump to the part in which I replicate the FILTER function's functionality for older versions, here is the link with the timestamp 49:22:
ua-cam.com/video/JTduguvrF34/v-deo.htmlm22s
Hope that helps you to implement the search box in your Excel version. I am curious to know, so feel free to give me another quick feedback whether it was helpful here or under the other video.
Thanks again and have a great day
Same with me. I don't have the filter function . I am right now using vlookup formula fir this.
Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
@theofficelab , you are so humble, absolutely a giver and helpful. Keep it up
A really good solution to my needs. I've seen it after searching a lot and it's great. Thank you very much.
Really good and clear tutorial, thank you
Well done, i follow your video and its amazing, thanks alot for this information and sharing 👍
4:32 I used this for my requirement tracker thanks dude
Great tutorial! Notes to myself, this part 1 tutorial doesn't convert source data into table yet (which part 2 does). So this tutorial works with absolute cells at this point.
Tried all 3 formula work perfectly fine
Thank you 🙏
Realmente muy interesante, en este momento lo pondré en práctica. Gracias por el aporte.
Many thanks prof !
This was so useful. Thanks so much!
you should re-post this, incredibly useful
Excellent Presentation and very useful video. Thanks.
You're simply a genius!
Great! Thanks.
The best of the best. Very clear and simple to follow. Amazing guys. Thank you very much.
Wonderful amazing you are the best
Very well engineered tutorial. ✌
Thanks! 🙂
excellent man 👌👌👌👌 very easy to understand your way of explaining things 💞💞
Great stuff. Amazing tutorial.
The two things I would do differently are:
1. Put the IFS function in the INCLUDE argument since the only thing changing is the search mode formulas.
2. Use SWITCH instead of IFS since your IFS conditions checks the same cell (for different things).
And now with the edition of LET and LAMBDA, I’d probably incorporate those too
Great sir
Beautiful
Sir! Not sure if it will be justified if i say thanks a lot for such life changing learnings.. Hv been using this n all other valuable training in my life and its really very helpful 🙏
Their is a very informative tutorial. I have a question, regarding the partial match function. Is it possible to have nothing displayed in the output table, rather than all data set, prior to input of search text. Thanks
Great video, however does this work for searching for numbers? I can get text to work no issue but csnt filter numbers
This is perfect.. :)
Nice
Hello, love this video. How do you create the active x textbox on Mac?
Hi Paul. I am afraid Active X elements are only available on Windows at the moment. I hope they introduce this for Mac as soon as possible!
great!!!
excellent Tutorial. any way to search by number versus text using the textbox?
That was exactly what I needed. Thank you very much! Just a question. If I have entries with punctuation. Could it be possible while I type/search, to return results even though I haven't typed the punctuation?
EXCELENT EXPLANATION
I DIDNT FIND THE FILTER FUNCTION ///
DO YOU HAVE ELTERNATIV FUNCTION FOR OFFICE 2019 PLEAS?
Can you apply this technique to a whole workbook? and how so would you do it
There is no Formula named FILTER in Excel, It Has FILTERXML only .. Which Version of Excel to be used ?
why does it show all data when the search box is empty? any fix to that? Thanks! Great Vid!
Would it be possible to limit displayed entries to 6rows while using partial match formula? Trying to add this search box into dashboard with limited space. Great stuff btw
hi, can you make a tutorial about inventory, where i can just search the item, then update the status,? thank you for your video, learn a lot.
Can anyone please tell me how one can search for numbers instead of text?
Also can this be used for those how have older excel (365/2019) please? So basically can this be done with VBA instead?
I don't think the FILTER function works on numbers. When searching a number, what I did was convert the original data set column with numbers into text, by using =TEXT(cell,"0"). Hope this helps you!
Thinks
how to filter numerical values?
Can i use checkboxes to search in different sheets if checked? Example: If first box is checked E1 is true and the search should do its thing in the first tabel, if second box is checked E2 is true and it should search in table 2. But if both are checked it should search in both. It should be something general because we can have 5 checkboxes..
I am using this help create schedules for our company. I want the search box in Worksheet A but want to search the staff list from Worksheet B. Is this possible? Every time I try I get errors and it won't allow to me to use the cell from Worksheet as my = to cell.
Can we have a video for the above same for Google sheet
What should I do to edit the search values
What if I add more data? do I need to retype the formula for the system to be able to search the added data?
did you figured out, I'm having the same query..!
is it possible to search from other sheet?
Hello, thanks for the video!
Is there a way we can copy the search results? Example: I use the search function, I get the result and I want to copy the text within a cell result (example, if it’s a link). Thanks!
i have the exact same question!
Great this function is only available in Office 365
Dear Sir , i need your Help to make one file using micro to copy multiline from one sheet to another sheet
Great! This is exactly what I was looking for. I do have one question if anyone would like to answer it. How do I get a comment to appear in array cell from the original cell it is referencing? Specifically, lets just say that in the comments were the cities these people lived in. Chris Miller's (A13) comment could be "Pittsburgh" and Leilas (A14) comment was "Melborn", how do I get that information to populate into the array?
Specifically, what I am doing is a catalog of parts. In it, I have information about the parts such as material used in construction, weights, applications, measurements... etc. I want these comments to be visible in the array. Thanks!
Really great tutorial. But how do I implement the result in the search area? I acutally want the same function as MS filter, but without the arrows on top of the columns.
will this work for multiple sheets in a workbook?
TIP: If your Formulas are not working even though they look perfect: try replacing the semi-colons with commas - it worked for me!
Underrated comment, Adam. Thank you 🙌
In my more recent videos I have switched to the comma notion since that's the one used in most countries.
Is this possible in open office?
Will this work in excel 2019 ?
is it applicable for 2013 excel version ?
We find, that using these kinds of formulas for search boxes dont work on sharepoint. Users who have the workbook open will get sync errors when other users search. Have you encountered this and is there a solution?
This is very helpful working on a bunch of data. Could you please make a tutorial on displaying a report file/excel file/daily report using its date. example..say..November 17, 2020. by using 3 combo box and 1 button that when i click to the button, it will display the file/daily report from a bunch of reports on a folder and when it displays the file it should have also a print button and a back to main button. and could you make it on an older excel version? mine is 2010. lol.
Hi office lab, wouldn this method be laggy if my data set is extremely large? Why don't I just filter and search from the filter directly?
Can this type of search be implemented using a data model?
How you make the exact match box where the search results are shown in?
Why does it not work when I try to use this between 2 tables. 1 data table and 1 results table. It will only return #value!
Can i apply this to microsoft excel 2010?
Hi Dell.
Unfortunately, Excel 2010 does not have the FILTER function that is used in this tutorial. However, there is a workaround to replicate what the FILTER function does. I explain this workaround in at the end of my latest video in the part covering 'Searchable Drop Down Lists'. Here is the link: ua-cam.com/video/JTduguvrF34/v-deo.html
The part about searchable drop down lists starts at 45:07 of that video.
Thank You :)
@@EngrWUAV Yeah same problem here. Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
how do you add the search box?
Hey, im using the real-time data search box for work and the filter works, but i got hyperlinks in my data and they dont get shown as a link to the website, is there any known workaround?
how to user this excel version for student or for free plz
in my excel 365 filter function not found..how to fixed? thanks
The FILTER function might not be rolled out to all Excel users yet. It depends on which updating cycle you have set in your office account settings.
To make sure your version is updated to the latest releases, you have to select the 'Insider Program' for the Updating cycles in your Account Settings. Here is a instruction from the official Microsoft Page describing how to do that: insider.office.com/en-us/handbook
Hope that helps. Don't hesitate to reach out if you have any further questions 😊
Yeah same problem here. Without 365 u can use vlookup and multiple match techniques. I found a video on a different channel viz. " Off to office" which I am recommending.
Is it possible to use the search box function to show results with clickable Hyperlinks? - We have a spreadsheet of drawings listed by part number in one column and a second column displaying links to the drawings but using this method doesn't allow the link to be clicked. Any help would be appreciated and thank you for this tutorial, very useful.
did you figure this out? I'm looking for same thing
@Mike S unfortunately not, searched around for a while finding nothing for what I needed.
Instead of having it populate in another table or area, I'm trying to have a smart search that filters the original data. keeping links
Excel is letting me dynamically search all my data which is perfect. However all the criteria in the table array is showing all the time instead of populating when I search for it in the search bar. How can I fix this?
This is a nice tool, is it also possible to select out of the found objects a name of number and transport it to a new cel?
I'm sorry sir..I'm still learning.. What formula you use in the cell F9 and G9??
best
I couldn't download the Worksheet, even if I signed up
The formula does not work when I type in but works when I copy and paste what I want to find. Can pls someone help me.
When Search bar is empty so in Search results area is showing " 0 " in all field
Hey there, I am having some trouble. The functions are working great, except when the cell is blank I am seeing the whole list. It does not use the "NO MATCH FOUND" that we entered in the [if blank] criteria. Any ideas why? How do I fix this?
Hi Stephanie,
I guess you are talking about one of the partial match variants, correct?! For these variants, if you have a blank search cell, the FILTER function searches for an empty string expression (""), which technically is part of every word. That's why the whole list is displayed.
To fix this, I you have to expand the 'include' expression with an IF statement that checks if the search cell is blank, like this:
=FILTER(array;IF(ISBLANK(search_cell);FALSE;ISNUMBER(SEARCH(search_cell;search_array)));"No Match")
Hope that solves your issue 😊
@@theofficelab Thank you so much, it worked perfectly!
@@theofficelab I used the Formula =FILTER(A9:C21,IF(ISBLANK(F3),FALSE,ISNUMBER(SEARCH(F3,B9:B21))),"NO MATCH FOUND") to test this out on the "Partial Match" area of the "Search Box" tab from the downloaded tutorial worksheet but it still displays all lines when the search box is empty what is wrong?
@@veteranconclave6159 Same Issue here! Cant get it to work.
@@theofficelab Hello, Im having issues with this and believe I figured out why... but unsure how to fix. Basically, if I do a =ISBLANK(search_cell) it returns FALSE even though the Textbox is "Cleared" (Nothing inputted). If I doubleclick into the search_cell and hit enter the =ISBLANK(search_cell) returns TRUE. I have not been able to figure out how to fix this though. I am hoping that the formula will see the search_cell as blank when the Textbox is blank Any help would be appreciated! Thanks.
Please I want a help
In Excel if I want to update a cell
(Old number +New number)
in real-time updation
Means going on adding new number to old number (existing)..
How can I do this .?
Please help if anybody knows ...
Hey man, thanks for the tutorial! Although I'm having some trouble with the Partial Search. I'm getting an VALUE ERROR, does anybody knows what could be wrong?
Same here! The Include element in the Filter function is a little weird
I have a excel 365, lately the filter function cant be recognized.
I tried and performed it on my data which exceeds 10000 rows it slow downs
I don't have the filter function
It doesn't work for me keeps showing error
will this work on excel 2019? (NOT 365 - no way i'd pay rent-for-life software lol). Also can you search for words/letters contained on your spreadsheet (either in entire sheet, or in the selected cells/columns/rows) and have your real-time results fill in the WHOLE page as you enter letters in the search box)?? - My sheets have many rows & columns so I need to see the WHOLE page/sheet while my results are narrowing down as I type. The little boxes you show are really not convenient for my use as they can show only very little. THANKS! (BTW I'm on a Mac - no more Windows for me since 2007...) :)
This only returns a #value! when i try it
Why do you say that you can download the template by subscribing? It does not work at all
can;t download :(
😲😲😲😲😲😲
What is this sorcery?
Cant change the video quality 😔
IT JUST DOESN'T WORK..... I THINK YOU SHOULD DO IT FROM THE BEGIINNING, LIKE WHEN CREATING THE TABLE, AND THOSE BOXES WHERE DATA FILTERED... HONESTLY, EVERY SINGLE TOTORIAL I HAVE SEEN, I'VE DONE EXACTLY WHAT VIDEO SAYS, BUT STILL NO LUCK... PLEASE SHOW US FROM THE VERY START.
******************************************