Forum Discussion
Excel search all columns in table and return value in first column of the row.
Hi all, First post to this site. Thank you in advance for the assistance! I am trying to setup a search box in excel. It is meant to do a text string search against all columns in a table, but ...
BTW, the original post said you wanted the return to be from a specific column so the formula returns ONLY the FullName value even if the match is in a different column.
I forgot to mention that the 'NickName' col in the attached example is the Name spelled backwards.
So a search for "ly" will return Teri Dactyl because the "nickname" is "lytcaD ireT" and a number of other matches too.
Also, you notice I have a 'helper' column called 'filtered list' that has that formula and then the Data Validation points to that column. I hope that helps.
note this attachment added the Data Validation part just in case it wasn't obvious.
david283 try this:
=FILTER(EmpList[FullName],BYROW(EmpList,LAMBDA(c,OR(ISNUMBER(SEARCH('Main'!C7,c))))),"Not Found")
Thanks for the reply.
That formula does search the first column 'FullName' and return those names that match, but it doesn't seem to search the 'NickName' column. Nick names that are present in the second column do not give results in the text search.
Unless it doesn't work the way i expect. when I type in the search box, as i start to type, a list box opens below and I see a list of matching names. The more I type the shorter that list gets as some are filtered out. If I am typing a name on the full name list, eventually my list of options drops to one or 2 and I can select it with a mouse click or TAB. But if I type a nick name, the options quickly drop to nothing, and I have nothing to select.
That formula does search the first column 'FullName' and return those names that match, but it doesn't seem to search the 'NickName' column. Nick names that are present in the second column do not give results in the text search.
Unless it doesn't work the way i expect. when I type in the search box, as i start to type, a list box opens below and I see a list of matching names. The more I type the shorter that list gets as some are filtered out. If I am typing a name on the full name list, eventually my list of options drops to one or 2 and I can select it with a mouse click or TAB. But if I type a nick name, the options quickly drop to nothing, and I have nothing to select.
