"ISNUMBER()" because the inner function "SEARCH()" returns the index number for the word. Therefore, when the search function returns the index number, the "ISNUMBER" will return "TRUE", and the "FILTER()" function will return the value for all the 'true's. TRUE() function could have been used instead of "ISNUMBER()" but it wouldn't be safe because sometimes anomalies could return as true.
Thanks Leila, I wouldn’t have thought of this approach. The ISNUMBER isn’t intuitive in this situation, but it obviously works!
You are a legend of Excel
LEILA IS THE LEGEND. WAS LOOKING AROUND FOR THIS ANSWER!!
Thank you for all your efforts teacher Leila from the US
New skills are gold! Thanks a million!
That's very useful. Thank you Leila. It would be great if Excel included Tags by default. I am sure there are ways of achieving something similar though and this video is a step in the right direction for me.
Thanks, brilliant..
Clever solution!
super works like a charm
Wow Leila. I am amazed to see how old excel formulas and so easily integrated with new excel i.e office 365
It's really not the wrong approach when countif and sumif work fine with "*". It's MS's inability to create a coherent product, probably because filter() was written by people who have never really used excel. Thanks for the tip anyway. MS is the enabler of gate keeping. Even though there are now tons of free tutorials l, of which we have another stellar example here, Excel is far from simple ad intuitive to use for most people.
Its a good workaround.obviously difficult to guess :)
Thanks, trying to understand why ISNUMBER works...
Astonishing content Leila 💯💯💯 Thanks to share your knowledge with us 👍
I wrote a custom function recently called contains that basically nests search and isnumber together. I feel like the word contains is a very natural language way to say what you want it to do. Just takes a couple of lines in VBA.
Thank you.❤
love it this will be my new search function from now on, i just switch the key word for search with a cell search box for custom entries, and fuse the 2 column of the array into one so i can search in both of them at the same time while returning both the column results, works like a charm for names + first names researches, and i can even hide the fused column for estetic and avoid repetition
This is magic. What should I do to be like this genius who can figure it out myself like a combination of functions
@carlwummel9050