Search Multiple Columns and Rows and Return Row

**rplute** · 02-20-2011, 11:57 PM

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______a@usa.edu_______Soccer
Row 2_____George_______555-555-5556______b@usa.edu_______Gymnastics
Row 3______Jill__________555-555-5557______c@usa.edu_______Soccer, Football
Row 4_____Ricardo_______555-555-5558______d@usa.edu_______Soccer
Row 5_____Henry________555-555-5559______e@usa.edu_______Track

Search: Soccer

Rows Returned:

_________Column J________Column K______________Column L_____________Column O
Row 1_____Jack__________555-555-5555_______a@usa.edu________Soccer
Row 3_____Jill____________555-555-5557_______c@usa.edu________Soccer, Football
Row 4_____Ricardo________555-555-5558_______d@usa.edu________Soccer

Thanks! Any help would be appreciated. I'm at a total loss.

**Marcol** · 02-21-2011, 06:14 AM

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

**broro183** · 02-21-2011, 06:32 PM

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

**Marcol** · 02-21-2011, 07:31 PM

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.

**broro183** · 02-21-2011, 09:58 PM

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