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.
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.
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.
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.
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'
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
