No announcement yet.

Lookup & Return All Occurences

  • Filter
  • Time
  • Show
Clear All
new posts

  • Lookup & Return All Occurences

    Hi Friends,

    This is my first request here,(previously I used to simply browse through and get whatever I wanted, given the vast topics Ozgrid covers, i have mostly gone back satisfied).

    I found this function that helps you lookup multiple occurences of a value andd return it in an array called Vlookups.

    However, unlike the original Vlookup, it does not support wildcards. I was hoping somebody could rewrite it to do so.

    My Situation.

    I have about a hundred cells of data in a particular column lets say B2:B101.
    Each cell has about 40-50 words of text.
    In A1,(the search box), I input the search term/keyword.
    In C2:C11, I want 10 occurences(if there are atleast 10) to populate from the data in B2:B101, irrespective of wherever the word occurs in the cell, and whether or not it is only part of a word.(the same results you would get if you ran a 'Find All' on that keyword)

    Am thankful for whoever can help me out,(need not be only through Vlookups)(please note that i have already tried out offset/match combo, it doesnt always return the correct results, specially when data is unsorted and when the keyword is not right at the beginning)

    Please help me out.

  • #2
    Re: Vlookups With Wildcard Support

    Hi gupta,

    Welcome to board!

    In C1,


    In C2 and copied down,


    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.




    • #3

      Re: Vlookups With Wildcard Support

      Hi Kris,

      Thanks for the welcome and the really quick reply.
      Last edited by Dave Hawley; August 15th, 2008, 10:25.