Announcement

Collapse
No announcement yet.

Lookup & Return All Occurences

Collapse
X
  • 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. http://home.pacbell.net/beban/

    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,

    =COUNTIF(B2:B101,"*"&A2&"*")

    In C2 and copied down,

    =IF(ROWS($C$2:$C2)<=MIN(10,$C$1),INDEX($B$1:$B$101,SMALL(IF(ISNUMBER(SEARCH($A$2,$B$2:$B$101)),ROW($B$2:$B$101)),ROWS($C$2:$C2))),"")

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

    HTH
    Kris

    ExcelFox

    Comment


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

      Comment

      Working...
      X