How to Create a Search Bar in Excel (in two minutes)

Рет қаралды 101,210

Күн бұрын

Пікірлер: 55

@mrlizard6529 6 ай бұрын

This works great. Couple of things to note 1. Spilling doesn't work in tables, so if you're using a template from excel that has tables in it and you're trying to return more than one column, it will give a #SPIILL error 2. Adding logic to the end to convert the boolean to 0 instead of 1 when the search field is empty ensures that no results are shown when the search field is empty 3. Enclosing the entire thing in an IF statement allows you to show text in the cell with the formula different than what is shown if no results are returned, such as "Please enter search criteria" Just a couple of ideas incase anyone else comes across this while trying to create a search box. Had no idea about the filter function. Good stuff.

@NicholasCornelius Ай бұрын

Worked on the first try and don't forget to close your formula with the appropriate number of closed brackets. Worked using a pivot table data set as well! Thanks so much.

@GFam0703 6 ай бұрын

Presented briefly and full of info. I don't know how you people figure all this out, but THANK YOU! 🤗

@marco89lcdm 2 ай бұрын

Thx, it works! On the origin table I’m adding some hyperlink, which are not being transposed to the search part once I search for a line. Is there a a workaround for this?

@vuitinh73 2 ай бұрын

Thanks for the nice instruction. I have a question that when I tried to create the search box in different sheet, it does not work. Could you please kindly advise

@tr3378 8 күн бұрын

You’re amazing! This worked great for me

@oxygendirect8588 9 ай бұрын

This is the formula I needed, thanks! I've tried exactly the same and it worked! So happy with this.

@rizwanqadeer2412 11 ай бұрын

Love your all tutorials! ❤

@szolfy 2 ай бұрын

Hi, great video. I have 1 search bar working against my table, however, my table consists of 21 columns. I want to be able to search from more than one column (happy to add a few additional search bars). Is this possible?

@Blahhhhhhhhh003 10 күн бұрын

I just tried and that's working well but i need to search The bar should be unique for each data. How can i fix that??

@papahetRout93 28 күн бұрын

what if you have and older version of Excel , that doesn't support the FILTER function ?

@ramseygr 11 ай бұрын

Very Nice! Is there a way to search across multiple columns? In your example, you're searching Representative (B5:B10004) - Is there a way to use the same search bar to look at other columns, like region for example? My goal is to search multiple columns in one search bar.

@ramseygr 11 ай бұрын

ah - I did figure out a way to 'cheat' - I created a column in my source table that concatenates multiple columns. ..works for my purpose anyway :)

@nicholashackie1407 10 ай бұрын

May you explain how you were able to. My issue is i have 22 columns with 10,000 rows each and my excel keeps freezing @ramseygr

@z9.b 8 ай бұрын

Copy paste the formula from the isnumber part then add a + and paste it with a new range at the end of ur formula but before the ,”none”)

@DesignerzNet 2 ай бұрын

hello, when done creating the search box, can or will you be able to edit the information you have search for ?

@DarylNotDead 11 ай бұрын

Came here from thread. Thank you. Can this be set to look for values on another tab in the same spreadsheet?

@brandiblankenship4892 8 ай бұрын

I would like to know this as well

@mortadhabatatia7833 11 күн бұрын

How could you put a search function in the table?

@RaffetAli2006 10 ай бұрын

But the filter function is not available in non-office365 excel. Is there any other alternative?

@medaminehaji8801 7 ай бұрын

tried it and working as magic, thanks for sharing

@Dsgagp 6 ай бұрын

I have 365 and It doesn't work.. I always get a value error.. works without being an insider member? Thanks

@khairulns8495 11 ай бұрын

this is what i want and finaly found tq

@cinspain08 3 ай бұрын

i have lots of info on my spreadsheets , i struggle to find the searched highlighted box as its just outlined with green lines is there a way to make the whole box green like on google sheets?

@jamilabelcher4998 3 ай бұрын

Can you show how to do a regular search box without the extra box to the right. Just a search box on top of a single chart.

@WillNutForFood 3 ай бұрын

I dont know what kind of wizardry you do, but for me it gives me an error that says I have "too few arguments for this function". Guess I need to stick to CTRL + F for now as this is too advanced.

@StevenHoaks 2 ай бұрын

I dont understand. Is this for some older excel? I do everything exactly as in the video but mine is giving "theres a problem with this formula" and gives me higlighted text (SEARCH(find_text; within_text; (start_num)) Cant get around this. What I am possible doing wrong?

@tom210gx Ай бұрын

I managed to get it to search another sheet, but how do I add a second sheet to the formula?

@JaneWade-dw2cl 6 ай бұрын

Does this formula work in excel 2010 and 2016?

@rachelcasemore5483 6 ай бұрын

Would this still work if the original data was on Sheet 2 and the Search was on Sheet 1?

@JerryFrench-v2z 6 ай бұрын

Do you have a template I could get hold of.

@FangRion 6 ай бұрын

Anyone know how it working on Excel 2003 ? It prompt me a result #Name?

@abdinasserabdow5626 Ай бұрын

thank you my angel 🌹

@susanmcrae7715 9 ай бұрын

not working for me. I have the formula =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:K300)),"None") and it is returning #VALUE. Could you tell me my mistake please?

@z9.b 8 ай бұрын

Same

@teachcaldschannel9819 8 ай бұрын

Found out the formula is incorrect. The second array should just be G5:G300 and not G5:K300. Change yours to =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:G300)),"None")

@z9.b 8 ай бұрын

@@teachcaldschannel9819 bro thank you so much its been a week trying to figure it out and i just saw this

@Raymondinnowicz 4 ай бұрын

@@teachcaldschannel9819 Lol, I had exactly the same problem with exactly the same reason. Thank you hahaha

@Zrksys 8 ай бұрын

why doesnt it work for me? just stays on #VALUE! all the time. is it cos the formula only works for 1 column or row for me? ye i have to add every column with a plus and change the array/range part. i add them with a + after )) before ,"none"

@bangru-nr2wv 5 ай бұрын

how to filter by numbers?

@nickbijl3142 11 ай бұрын

It is not working for my version of excel. =ZOEKEN (search in dutch) returns only the exact name when typed completely in the search. Not a numer. Also not able to type in the searchbox. Just in the cel linked to it. The text does end up in the textbox after typing in the linked cel.

@PatriciaKees 3 ай бұрын

Not sure if you are still struggling with the formula, but in Dutch the SEARCH formula is not ZOEKEN, but VIND.SPEC. So the formula should be something like this: =FILTER(A6:J290;ISGETAL(VIND.SPEC(B2;I6:I290));"Geen gegevens"). Bij mij werkt dit wel, ook met gedeeltelijke overeenkomsten in het overzicht.