+ Reply to Thread
Results 1 to 5 of 5

Search Multiple Columns and Rows and Return Row

  1. #1
    Registered User
    Join Date
    02-20-2011
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2008
    Posts
    1

    Search Multiple Columns and Rows and Return Row

    Hey All,

    I would first like to say thank you in advance for any help as I am new to Excel.

    I have attached a worksheet to help visualize what I am asking.

    What I want is a Search Box in I1. If you were then to type in a keyword or inquiry into the box and press enter, and if that keyword was found in the data set it would then return the entire row of information in which the keyword was found. If the keyword was found in multiple rows, then it would return all the rows in which the keyword was found.

    The data set in the worksheet is A2:G104.
    I would like the returned rows starting from J2:P104. So they're basically side-by-side.
    The Search box would be in cell I1.

    An example would be,

    Data Set:
    _________Column A ______Column B____________Column C______________ Column D
    Row 1_____ Jack_________555-555-5555______[email protected]_______Soccer
    Row 2_____George_______555-555-5556______[email protected]_______Gymnastics
    Row 3______Jill__________555-555-5557______[email protected]_______Soccer, Football
    Row 4_____Ricardo_______555-555-5558______[email protected]_______Soccer
    Row 5_____Henry________555-555-5559______[email protected]_______Track

    Search: Soccer

    Rows Returned:

    _________Column J________Column K______________Column L_____________Column O
    Row 1_____Jack__________555-555-5555_______[email protected]________Soccer
    Row 3_____Jill____________555-555-5557_______[email protected]________Soccer, Football
    Row 4_____Ricardo________555-555-5558_______[email protected]________Soccer

    Thanks! Any help would be appreciated. I'm at a total loss.
    Attached Files Attached Files
    Last edited by rplute; 02-21-2011 at 01:02 AM. Reason: Formatting Issue

  2. #2
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Search Multiple Columns and Rows and Return Row

    Hi rplute

    Welcome to the forum.

    Try this workbook

    Type your search word in M1 (Yellow Cell), delete to clear the filter

    This Filters on the Sheet Module > Worksheet_Change Event
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  3. #3
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Search Multiple Columns and Rows and Return Row

    hi,

    This "example search function.zip" file that I put together a while ago may work as an alternative to Marcol's suggestion. It will need some modifications, but have a go at making the changes & let us know if you have any questions: http://www.excelforum.com/2208558-post25.html

    Marcol, I think the OP wants the Search Cell to check every column - but I could be wrong ;-)

    hth
    Rob
    Rob Brockett
    Kiwi in the UK
    Always learning & the best way to learn is to experience...

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Search Multiple Columns and Rows and Return Row

    Hi Rob

    You could well be right about the OP wanting to search more than one column, using a simple macro to filter the data just seemed an easier opening gambit based on the sample given.

  5. #5
    Forum Expert
    Join Date
    01-03-2006
    Location
    Waikato, New Zealand
    MS-Off Ver
    2010 @ work & 2007 @ home
    Posts
    2,243

    Re: Search Multiple Columns and Rows and Return Row

    Only an opening gambit...?

    I was trying for game, set & match in my first response - wishful thinking I know, esp as I provided an unmodified example that's over a year old!

    Rob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1