Forum Discussion
david283
Apr 11, 2022Copper Contributor
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 ...
- Apr 12, 2022
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.
mtarler
Apr 12, 2022Silver Contributor
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
Apr 12, 2022Copper Contributor
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.
- mtarlerApr 12, 2022Silver Contributor
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'
- david283Apr 13, 2022Copper Contributor
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
- mtarlerApr 13, 2022Silver Contributor
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.