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