Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Lookup & Return All Occurences

  1. #1
    Join Date
    14th August 2008
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  3. #3
    Join Date
    14th August 2008
    Posts
    2

    Re: Vlookups With Wildcard Support

    Hi Kris,

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Lookup All Occurences & Merge Results
    By Kath in forum Excel General
    Replies: 8
    Last Post: October 25th, 2008, 04:36
  2. Return All Matching Occurences
    By rpancheri in forum Excel General
    Replies: 4
    Last Post: April 16th, 2008, 10:00
  3. Replies: 8
    Last Post: April 11th, 2008, 01:15
  4. Return Lookup Results Of All Occurences
    By Windy58 in forum Excel General
    Replies: 9
    Last Post: January 31st, 2008, 06:04
  5. Lookup All Occurences of Repeated Values
    By Ken Monville in forum Excel General
    Replies: 1
    Last Post: December 2nd, 2006, 14:23

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno