search function for vlookup

  • My question is if there is a way to combine the SEARCH function with VLOOKUP or INDEX and MATCH. I know how to use SEARCH to find where "Library" starts in "AL Public Library" but what I want to be able to do is search a column for text and have it return all results where the text I enter is in the cell. This way, if I type "Library" every cell with the word "Library" somewhere in it will be returned. I hope this explains what I am trying to do. I am attaching an example to help explain further.


  • Hi Steve,

    Advanced Filter should do what you want. Just use the wildcard symbol before and after your search criteria, i.e. *Library*.

    It seems that the wildcards will also work in Auto filter, using the custom filter.

    They can be used in a VLOOKUP formula, but it only returns the first match.

    Rich D

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • the only problem with using the advanced filter is that I am going to be looking up over 5000 entries and I dont want to have to use the advanced filter each time. I'd much rather have a way of just typing in the word and having a formula do the rest...I'll try it but I'm not sure if it will work the way I need it to. Thanks though

  • i just went through it and although that was not the method i was going to use, it definitely seems easier to just write a macro using the advanced filter than to do what i have done. thanks for help!


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!