Forum Discussion
Excel search all columns in table and return value in first column of the row.
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.
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?v=Z-h2UER3b_0
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.
- OK, I see, thank you mtarler
And that clued me into what's going on. My desktop excel version is the Beta channel release, I have it because the Access in beta channel has some new dataverse features I am testing. And it also has the Excel autocomplete dropdown features you are describing from the online version.
I think when I tried your first formula above I was at home. My home PC is on normal release versions and when I didn't get the auto complete / drop down I was expecting, I attributed it to your formula.
I wont be moving my users to the beta version, so I wont worry about the dropdown autocomplete any more. So I just spent the last hour testing various versions of your formulas and table in a normal excel desktop version, and your first formula above is my favorite. I think it will meet our needs and is very user friendly. And it does offer the full user name even when a nick name is typed for the search, which was the feature we were looking for.
So that solves it. I am going to put that into my live sheet and begin testing for production.
Thanks you very much for all the help 🙂