Unlock Excel Secrets: Magic Search Bar You NEVER Knew About!

[@tonyross7550]

Very clever. A lot of relatively simple techniques combined to create a complex solution.

[@PKAnExcelExpert]

Glad you liked it!

[@osoriomatucurane9511]

Awesome, you are truly undisputed and unmatched Excel Expert. The use of named range for referencing both ranges and formulas is amazing. Keep it up

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@finguy8572]

This is probably the best Excel video I’ve ever seen! I am now looking for any excuse I can come up with to use it!

[@PKAnExcelExpert]

Thanks

[@RAMESHRAMESH-un2gp]

Wow... Headache of VBA solved... brilliant & simple..for Novice.. Hats off..

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@dmkdi]

You just solve my whole problem. Searched this since 6 months ago. Even chat gpt didn't understand my problem. Thank you sir... ❤❤❤

[@PKAnExcelExpert]

Most welcome🙏

[@oh_heyydeoo]

This is a great video for anyone who does not understand or do not want to write out VBA code. I'm glad this is here for anyone to take advantage of it. But hear me out. It's still best to learn Visual Basics for Excel regardless of how many new features are out. Taking that time out to really learn code helped reduce time for me as well reduce a lot of repetitive task so much easier. But overall, great video for anyone else!

[@PKAnExcelExpert]

Thanks

[@osoriomatucurane9511]

Savvy, outstanding! Unmatched or unparalled skill. PK, the Excel Legend

[@PKAnExcelExpert]

Thanks for your valuable feedback 🙏

[@sketchtwenty2]

Ingenious method for searching very clearly explained.

[@PKAnExcelExpert]

Thanks

[@IsaacAkowe-l5o]

YOU'RE GOOD! From Nigeria

[@PKAnExcelExpert]

Thank you!!

[@D3vinG]

This is exactly what I was looking to do, thank you my friend!

[@PKAnExcelExpert]

Most welcome

[@rikusmellet919]

Brilliant! Thanks, I searched for a solution on KZbin for days and only found part of the solution in most videos I saw.

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@jaikritbhattacharya4341]

Brilliant and very catchy... learned something. thanks

[@PKAnExcelExpert]

Thanks🙏

[@samprime3180]

It is very very nice to sit and watch the amazing things we can do with excel. Big Salute.

[@philipjacob1979]

Thank you PK. Excellent. Keep up the good work.

[@PKAnExcelExpert]

My pleasure

[@sakists6870]

Excellent job! Thank you so much!!!

[@PKAnExcelExpert]

You're very welcome!

[@mohammedhassan2808]

Superb idea and technique, Great work without using any coding, excellent

[@PKAnExcelExpert]

Thanks🙏

[@ikinursiyanto-dj7500]

Amazing..great...thank you very much

[@PKAnExcelExpert]

Most welcome🙏

[@PVMiroshnyk]

Wow! This is an incredible combination of quite simple elements! I applaud standing! PS I just don't know where to apply it, but it's a very beautiful and elegant solution

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@Vijay_krm]

Great sir ji...Really appreciative

[@PKAnExcelExpert]

Thanks🙏

[@ashusharma9439]

This is Amazing Thanks PK.

[@PKAnExcelExpert]

Most welcome🙏

[@RichJB1973]

Thank you that’s going to help me a lot

[@PKAnExcelExpert]

Most welcome🙏

[@misdin1]

Very nice video, very good, incredible trick. Thank you very much 👍

[@PKAnExcelExpert]

Most welcome🙏

[@bilalsheikh9127]

Totally impressive. Stay blessed. Such excellent and elaborated explanation of each step.

[@PKAnExcelExpert]

Thanks🙏

[@lebogangmetsileng8969]

wow this was soo informative. Thank you

[@PKAnExcelExpert]

Most welcome

[@Pete128]

Simply brilliant. Thank you !!!

[@PKAnExcelExpert]

Most welcome🙏

[@Masdille]

What excel does he using

[@sculpture2582]

Many Thanks from Germany 👌👍💪

[@PKAnExcelExpert]

Most welcome🙏

[@adeshraka6668]

Very well explain in this vid..Sirji

[@PKAnExcelExpert]

Thanks🙏

[@adeshraka6668]

@@PKAnExcelExpert how to search eg..Type ..A B C and then Result ADi Bike Care...(Left Mid Right name search)

[@SM-tz1hc]

Very beautiful... With respect

[@PKAnExcelExpert]

Thanks a lot🙏

[@muhammadashraf1624]

Very informative video. Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@hometv-sr]

Wow wow wow. Never knew some of these techniques. Excellent video. Loved it man. Thanks for sharing these. You earned a follower. Have followed you. Keep it up.

[@PKAnExcelExpert]

Thanks a lot 👍

[@zaydarendse2812]

amazing!! Thanks so much for sharing :)

[@PKAnExcelExpert]

Most welcome🙏

[@q8paramedic]

Very good work I learned from you new thing ❤

[@PKAnExcelExpert]

Thanks🙏

[@rajsahu1029]

Doing very well. Wery interesting, useful and beautiful. I have seen several persons videos but no one is so beautyful work representation of figures/Data and chars I have seen from others. Thanks very much to you.

[@PKAnExcelExpert]

Most welcome🙏🙏🙏

[@freelancing8605]

superb hats off sir.... thanks

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@excel168]

Nice lesson. Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@jackcurl2005]

Great explanation, clearly demonstrated. In general, perhaps use IFS or SWITCH(✔) rather than nested IF statements-it's cleaner, easier to debug, easier to read, and certainly easier to program. Both functions became available with Office 2016. If you have FILTER, you have these functions (and a slew of other niftiness). An example for those who might wondering: SWITCH($A$1,1,Search_Left,2,Search_Right,3,Search_Anywhere,"No results") The test/result for the value 3 is included to handle invalid data. It's better to specify than to default. Since A1 is an openly editable cell, any value could appear there. We need to take that into account. Never trust users (even oneself) to always do it right. As the saying goes, An idiot-proof system just hasn't been introduced to the correct idiot yet.

[@md.tofaelahmed6410]

💯 Outstanding Boss

[@PKAnExcelExpert]

Thank you so much 😀

[@kdstudiolab]

nice sharing..thank you

[@PKAnExcelExpert]

Most welcome

[@mahargleopenaws]

Fantastic teacher

[@PKAnExcelExpert]

Thanks🙏

[@simpabar1066]

Amazing video thanks 👍👍👍

[@PKAnExcelExpert]

Glad you enjoyed it

[@TampaRob84]

FYI, for the helper cells in row 1, custom format of ;;; makes the text invisible rather than white so it won't display if the cell color is changed

[@soyoyeoluwafunso4586]

This is great very helpful

[@PKAnExcelExpert]

Glad it was helpful!

[@gksnaimisha]

😮 superb demo

[@PKAnExcelExpert]

Thanks

[@learnverntoz]

Amazing.... As usual....👌👌👍👍

[@PKAnExcelExpert]

Thank you so much 😀

[@nadermounir8228]

Brilliant PK greatly enjoy the video 📹 👍 keep up the good work. We really thank u for your hard work

[@PKAnExcelExpert]

Most welcome🙏

[@cesarminiao]

You’re a genius!

[@2000sunsunny]

Thank you for video. I wıll definetely use name manager 🙏

[@PKAnExcelExpert]

Most welcome🙏

[@info2satish]

Awesome pk bro!!

[@PKAnExcelExpert]

Thanks ✌️

[@ksumar]

Genius Sir... I salute you! I'll save this video. 👍

[@PKAnExcelExpert]

My pleasure 😊

[@unnikrishnansanthosh]

class one, thank you for sharing

[@PKAnExcelExpert]

My pleasure!

[@rizwanghauri]

brilliant work ... is that also possible (off course in the same simple way) to get the same search records by taking the input search text from multiple columns of data?

[@paca-san]

Thank you for this, very informative and helpful. I have a question; would this be usable if for example I have multiple sheets of data?

[@sunnindawg]

Nice! No more need for filters.

[@PKAnExcelExpert]

Thanks

[@NupurAdronPharma]

Thank you so much🧡🧡

[@PKAnExcelExpert]

You’re welcome

[@jimmytee8913]

This is amazing, I have been looking for something like this, we are are creating a mini database for a running club, with this video it going to be a game changer. Thank u ok u so much for that's awesome video

[@PKAnExcelExpert]

Glad it was helpful!

[@jimmytee8913]

Can this be done in google sheets?

[@SaloniAgraDepot]

ZABARDAST SIR.............

[@PKAnExcelExpert]

Thanks

[@silvestrecamposano6317]

Thank you very much!.... ❤

[@lukasolsovsky7299]

Very well done sir !! I´m used to use it indirect and named_ranges but with FILTER formula it has another level ! Thank you for your video. BR from Czech Rep.

[@PKAnExcelExpert]

Most welcome🙏

[@darwinalbo9491]

Thank you friend

[@PKAnExcelExpert]

Most welcome

[@gospelmoto2833]

Nice video! you got a new subscriber here. I like the way you teach - very clear and straightforward.

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@theexposer9483]

Very well balanced video in terms of content, speed of delivery, movement of screens and clarity of subject. Take my complement for the good work. This is my first video from your channel. A suggestion which would increase your viewership , since it would help many users for their work. That is , if you can create an equivalent sheet in Google sheet for all your work and place grouped together with excel version. I don’t know your expertise level on Google sheets because both these applications run on different platform and underlying language. If you give it a try it would be wonderful gift to your viewers. God bless you.

[@PKAnExcelExpert]

Thanks for your valuable feedback. I will definitely try to create.

[@somenmaji7078]

true, this way less complicated. I tried making a same type, which tells where a name falls in a number of sheets in the same workbook. I did that using table and DGET() function.The one you made is very well balanced and very less complected.

[@querrythis]

Highly recommend this dynamic search. The best (hands down) I've come across.

[@PKAnExcelExpert]

Thanks a lot.

[@Edison_mm]

So great! Thank's ¿Can I use Choose function ?

[@jothidarsubha.kalaichelvan8068]

Wow...what a smart formulated illustrations...❤

[@PKAnExcelExpert]

Thanks🙏

[@mpscqa7387]

Excellent 👌

[@PKAnExcelExpert]

Thank you! Cheers!

[@ahmedmallook4128]

Thanks for the tutorial, PK. I have a question: How can I change the font size of the selection buttons? Search from Left, etc?

[@nadahkhi]

Excellent work

[@PKAnExcelExpert]

Thanks🙏

[@irfanchauhan313]

Great Sir.... We have improved our knowledge by watching your videos and Practice file..... Thank for you Everything..... I really appreciate your contribution for us and all @ FREE OF COST..... God Bless You Sir 🙏

[@PKAnExcelExpert]

Thanks a lot🙏

[@kokyaw9330]

sir i want to know how to do. if i input 1234 in a cell, i want out put like 11 12 13 14 21 22 23 24 31 32 33 34 41 42 43 44

[@chanman7038]

Hi PK, thank you for the video. I had question at 8:10 . When I key F4 and enter, I only got one data on cell b5. It doesn’t show the whole list.

[@mayankminati9456]

Bro I also facing same problem u get the answer or not?

[@chanman7038]

@@mayankminati9456 not yet

[@shreeramsahani2543]

Me too

[@mohammadilyas2260]

Excellent زبردست

[@PKAnExcelExpert]

Thanks🙏

[@6ugggg]

useful work.thanks

[@PKAnExcelExpert]

Most welcome🙏

[@chandanmhatre915]

Great as always ❤

[@PKAnExcelExpert]

Thanks🙏

[@KONDOMBOADAMA-ru9iq]

Merci beaucoup pour le contenu

[@PKAnExcelExpert]

Welcome

[@WaymondJr]

This was fantastic ♥️

[@PKAnExcelExpert]

Thanks🙏

[@Eddy2093]

Cool for small data sets, very resource hungry for large data sets.

[@PKAnExcelExpert]

Right

[@alterchannel2501]

Good job! The best formula based search bar👍👏

[@PKAnExcelExpert]

Thanks🙏

[@1724joshua]

This opens a lot of ideas on how to improve my work. I hope you can make a video on how to do the same function but searching and gathering data from different sheets.

[@PKAnExcelExpert]

will try to create another video on this.

[@darshanpatil4347]

Awesome!!

[@PKAnExcelExpert]

Thanks!

[@uktammadraimov]

Thank you Bro

[@PKAnExcelExpert]

Welcome

[@markus_aurelius_888]

Great vid, fantastic that you use formulas instead of programing, it makes life so much easier. 👍

[@PKAnExcelExpert]

Thanks for your valuable feedback🙏

[@dangerlu7635]

Great video to learn, by the way ,how to make the search icon and shapes unselect in excel?~

[@pandharinathjoshi6565]

Sir reallu ultimate sir

[@PKAnExcelExpert]

Thanks🙏

[@imranbhatti8580]

Realy appreciated. Any chance we create it in non 365 Excel?

[@grijalvaescobedo158]

Claro si no tienes 365 con filtro avanzado el tradicional compañero saludos

[@niteshjaiswal2754]

Excellent 👌🏻❤

[@PKAnExcelExpert]

Thanks

[@wilfredocaong]

Wow! This video/function is very impressive.. Just a question.. If this function is available only on 2021 office version, can the file work on a 2019 office version..? Thank you so much and more power..

[@4567snick]

Great video, I learned a lot! Any reason why you use named ranges for the data rather than converting the data to a table? I use both, but I would have probably used a table in this case. Thanks for the great lesson!

[@PKAnExcelExpert]

We can use the table also. Thanks for your valuable feedback.

[@somenmaji7078]

thxs

[@jackcurl2005]

Tables are fantastic! I had the same thought about using them.

[@hafizahrahmat7421]

Thank you for a very clear tutorial. May i know how to search the specific data within the type. Eg: serch name for under the same supervisor.

[@grijalvaescobedo158]

Gracias maestro por compartir excelentes tips saludos 🇲🇽👍👋👋👋👏

[@PKAnExcelExpert]

Gracias por ver

[@9901028711]

You should be on Moon One of the top rated excel teacher

[@PKAnExcelExpert]

Thanks for your valuable feedback

[@JaMarc_1718]

Wow what a great video. I've learned a lot. I already knew individual techniques - but in this video everything was put into a clever context - and a lot was also new to me. Thanks!🤩

[@PKAnExcelExpert]

Most welcome🙏

[@bijaytanty8434]

Thanks

[@PKAnExcelExpert]

Most welcome🙏

[@akavaramvallabhai4950]

EXCELLENT

[@PKAnExcelExpert]

Many thanks!

[@Theoneprogamerr]

Thanks alot

[@PKAnExcelExpert]

Most welcome

[@mohamedlakel7084]

wow greatful

[@PKAnExcelExpert]

Thanks a lot🙏

[@hassanalianees5530]

Brilliant

[@PKAnExcelExpert]

Thanks 👍

[@JohnKnorr]

Great tutorial. Is it possible to protect the worksheet but still allow the search to work? I want to share a searchable list, but don't want anyone breaking the file.

[@pankulbaijal7542]

Great Technique sir and very impressive, just a small question that will this this work similarly good when there Merge cells available in the data. Please answer this question. Thanks Happy Learning😊

[@soniccheese01]

Nice one. Here is another example formula that uses a Table called MyData and searching a column called Company: =SWITCH(A2,1,FILTER(MyData,LEFT(MyData[COMPANY],LEN(SearchText))=SearchText),2,FILTER(MyData,RIGHT(MyData[COMPANY],LEN(SearchText))=SearchText),3,FILTER(MyData,ISNUMBER(SEARCH(SearchText,MyData[COMPANY])))) No need to use additional Range Names, or particularly the INDIRECT function Any thoughts?

[@PKAnExcelExpert]

Certainly! Your comment is insightful and adds valuable information to the discussion. I appreciate you sharing this alternative method using the SWITCH function and Table. It's always great to see different approaches to a problem. Thanks for contributing to our community!

[@NurHafizah-r9y]

hi nice tutorial. do u have an option for excel 2013? thank you

[@thehunternags2]

Hello Brother PK, I would request if you can create some template in which it contains the question and answer as Yes, No & NA, using the radio button but at the final stage it should support if I want to run a pivot table to run it as a report.

[@PKAnExcelExpert]

Will try to create

[@thehunternags2]

I really like your quick response bro​@@PKAnExcelExpert