Help creating search box

Hello all. I am using http://www.excel-easy.com/examples/search-box.html as a reference and I almost have it figured out...but not quite. In the attached file, I want the matching names from column "A" to be displayed in column "E" where the following formula resides:

=IFERROR(VLOOKUP(D4,$A$4:$C$500,-3,FALSE),"")

I used -3 thinking the column being referenced was the one I wanted to use to get the matched results. Any help is appreciated.

Andrew

Hello Andrew
Take a look at the attached example. I've changed the formulas (blue cells) to how I would perhaps do this as your Vlookup formula won't work in the way you are using it with a negative column reference.

Hope this helps.
DBY

Thanks for the quick reply and solution DBY!! It works as desired on the test file so time to try it on the production file.

Glad to have helped, good luck with it and thanks for the Rep mark.

If you want the matched results in column A then you're going to have to use a filter to display them as obviously formulas there are out of the question with that column being the lookup source. You can filter on the 'Row Index' column with a Custom filter >0.

Maybe this is better done with VBA?

DBY, I assume the 'Row Index' is column 'B' but do not know how to add a custom filter. I tried adding >0 to a couple places in

=IF(ISNUMBER(SEARCH($D$2,A4)),ROWS(A$4:A4),"")

but I can't get column 'A' to only display the names in the search field 'D2'.

Found

HTML Code: 

for advanced filters but could use someone more experianced than I to implement this into my example. I tried putting a heading 'Links' in column 'E' which is where the matches currently display but the advanced filter did not recognize the generated data. I tried making column 'B' the key field by giving it a heading and making 'C3:C4' the same header and >0 but none of the data in column 'A' is being displayed unless I remove the advanced filter, probably because column 'B' is calculated.

I will use the search you provided for another part of the spreadsheet and will find a VBA solution to tweak this. If you have something you think can get me started it would be great. I will close this thread in a couple days. Thanks.

Hi
See the two attached files for a couple of VBA examples of how I might do this, as I said though I'm no expert, despite what my profile states. The first uses the Advanced Filter with a search/criteria cell and the second uses a search cell and the Index column with formulas to filter by >0.

Thanks DBY!! I think I like the one without the Advanced Filter since I can enter any part of the word and just press enter.

Not sure why but the advanced filter example only works if the search matches the words in the order of the letters in the word...so "S" would find Sam Smith...but "sm" does not find "Smith" in Sam Smith.