Excel search all columns in table and return value in first column of the row.

[david283]

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.

Any ideas?

[#2]

@david283 try this:

=FILTER(EmpList[FullName],BYROW(EmpList,LAMBDA(c,OR(ISNUMBER(SEARCH('Main'!C7,c))))),"Not Found")

[#3]

@david283 

This is a good one and a big reason I enjoy Excel.  There's more than one way to pull this off.

My solution:

[#4]

Thanks for the reply.
That formula does search the first column 'FullName' and return those names that match, but it doesn't seem to search the 'NickName' column. Nick names that are present in the second column do not give results in the text search.

Unless it doesn't work the way i expect. when I type in the search box, as i start to type, a list box opens below and I see a list of matching names. The more I type the shorter that list gets as some are filtered out. If I am typing a name on the full name list, eventually my list of options drops to one or 2 and I can select it with a mouse click or TAB. But if I type a nick name, the options quickly drop to nothing, and I have nothing to select.

[#5]

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?

[#6]

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

[#7 · Accepted solution]

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.

[#8]

@mtarler 

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.

[#9]

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

[#10]

@mtarler 

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

[#11]

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

[#12]

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 🙂