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 return the value in a specific column if there are any matches in that row.
My table (data source) is generated by Power Query from an external CSV file, and periodically updated, but I don't think that effects this formula. This table is on the 'EmpTab' sheet.
The table is 'EmpList' and it has the columns 'FullName', and 'NickName'
I have my input cell where the user can type the string to search, It has data validation set to look in my formula cell on another sheet. This search box is on my 'Main' sheet.
My formula is this: =FILTER(EmpList[FullName],ISNUMBER(SEARCH('Main'!C7,EmpList[#All])),"Not Found")
If I set my search to FullName instead of #All or NickName, it works. It also works with #Headers, but then the headers also show up on the list, and both the FullName and NickNames show as choices in the search results. #Data doesnt work at all.
Any ideas?
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.
- 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.
This is a good one and a big reason I enjoy Excel. There's more than one way to pull this off.
My solution:
Thank you Patrick.
Your solution looks interesting, but my 'being kind of new at this' is getting in the way, I am having some trouble translating this back to my use case.
I think your formula is this: =LET(I,INDEX(agent,SMALL(IF(rng="A",ROW(rng)),SEQUENCE(1000))),FILTER(I,NOT(ISERROR(I))))
So for me to use this with my table and worksheets, I need to replace "agent" with my table EmpList, and I need to replace your search string "A" with my worksheet and cell 'Main'!C7. So my formula should be:
=LET(I,INDEX(EmpList,SMALL(IF(rng="'Main'!C7",ROW(rng)),SEQUENCE(1000))),FILTER(I,NOT(ISERROR(I))))
But this is giving me errors. Do I have the syntax wrong?david283 I'm not sure why my formula didn't work for you. I am attaching a sheet with it working (the only exception is the reference cell)
I also played with Patrick2788 formula and there were a couple of things I fixed:
=LET(I,INDEX(EmpList[FullName],SMALL(IF(ISNUMBER(SEARCH($F$1,EmpList)),ROW(EmpList)-ROW(EmpList[#Headers])),SEQUENCE(ROWS(EmpList)))),o,FILTER(I,NOT(ISERROR(I))),o)David, your errors included putting Main!C7 inside "" and not replacing the rng with the range to check (i.e. EmpList).
That said the equation was using X=Y which was fine in his example where his table was full of only A or B but needed to be tweaked to ISNUMBER(SEARCH(....)). I also changed the SEQUENCE(1000) which hard coded it to 1000 lines to be SEQUENCE(ROWS() ) to dynamically adjust the length. I also had to adjust the ROW() to subtract the ROW( ... headers) to align correctly (not a problem is the range started with row 1.
That all said, Patrick's formula is nice but it is returning duplicates for the same row. Not sure if I did something wrong but see attached.
