Apr 11 2022 09:31 AM - edited Apr 11 2022 12:11 PM
Apr 11 2022 09:31 AM - edited Apr 11 2022 12:11 PM
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.
Apr 11 2022 11:28 AM
@david283 try this:
Apr 11 2022 12:14 PM
This is a good one and a big reason I enjoy Excel. There's more than one way to pull this off.
Apr 11 2022 12:31 PM
Apr 11 2022 12:58 PM - edited Apr 11 2022 01:02 PM
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:
But this is giving me errors. Do I have the syntax wrong?
Apr 11 2022 05:56 PM
@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:
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.
Apr 11 2022 06:06 PMSolution
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.
Apr 12 2022 02:18 PM
OK, got a chance to play with your sheet and try it out. Amazing formula and I can see now that it works, and was actually working for me before. It just wasn't working with "Data Validation" the way I expected and that was throwing me off.
So on your sheet and on mine I can use data validation on the search string cell and point it to the formula cell, and as I type names from the FullName column it will show me a list of names that shortens as I type. I can even tab to auto fill the top name into my search box. Also, looking over at your formula table, its filtered down to the name I typed/selected.
But its a little different for NickNames or other columns. If I type a nick name and then tab, your formula does work, I can see the shortened list under your formula with the corresponding full name. But data validation doesn't pick it up as a name to offer in the search string box. And if I hit tab I get nothing, because the NickName wasn't a valid option.
So its really an issue with data validation, not your formula. Not sure what to do from here. I will probably give up on the search both full name and nick name idea. Users will just need to start using full names only.
Thank you everyone for giving this a try.
Apr 12 2022 04:14 PM
@david283 don't give up. I see what you are doing. I'm not used to using the online live search. So the version I gave you works find if you type your initial letters and click the down arrow (in the desktop version) or hit the checkmark or hit enter in the online version and then click the down arrow. What you want is actually a lot easier I think. In the attached I created a helper column that creates:
FullName ( NickName )
Then the data validation points at that column and it will work the way I think you want.
I then added another cell below it called Actual Name that then strips off the ( Nickname ) portion.
BTW, the original version will find ANY name with those letters ANYWHERE in the name.
This live version appears to filter based on only Words Starting with those letters. i.e. 'at' will fine 'Athena Smith' and 'Mike Atticus' but NOT 'Matt Matthews'
Apr 12 2022 05:16 PM
I am playing with your new version now to see if I can do what I want with it.
Our team doesn't use the online/web browser version of excel at all, we use the windows full excel program only. Microsoft 365 standard version.
this is how I enable data validation =
Don't know if it will help or not, but I was following this video at first to set this up. This works well, but I was trying to expand it to search both name fields, as we have a lot of nick names in our small company. https://www.youtube.com/watch?v=Z-h2UER3b_0
Apr 12 2022 08:53 PM
@david283 Yes that is how you add Data Validation but the difference in how it works for the user is that:
In the online/browser based version of Excel I can click the drop down arrow and get the list and then start typing and that list will only show 'matching' values. This image shows the browser based functionality as I typed 'te' into that cell the drop down went from all the list down to only 4 names:
In the full desktop application if I click the drop down menu first it will show me the full list and I can NOT type. If I type first (e.g. 'te') then I can click the drop down menu (which causes the value 'te' to be committed to the cell) and then it will show me the drop down list according to where the data validation rule points to. In my first example that data validation pointed to a dynamic list that would update based on what was entered into that cell. But that filter action does not happen until you click OK or something and 'commit' a value to that cell.
So your description above about as you type the list shrinks sounds more like the functionality provided by the online/browser-based excel. And then you say when you click tab it enters that top value, also functionality consistent with the web-browser-based version.
As yet one more variation and attempt, in this attachment I combined both so now the search box validation is pointing to a combined list (with FullName(Nickname) ) that also dynamically filters...
The problem with this version is for the online/browser version, you will need to make sure to clear the search box 1st otherwise it will only see and do the dynamic searching of the already filtered list from the prior entry.
Apr 13 2022 09:39 AM