Array formula to return any cell vales that contain specific text

[Rusty315]

Hi I have a table (A3:H500) where I have a list of writer's names in column A and each writer's specialties in column H (columns B to G contain personal info that's not relevant for this formula). Examples of specialties in column H are things like 'Australia', 'Travel', 'South Coast', 'Food and beverage', etc. Writers could have several specialties so the entries in column H might be anything from a single word to phrases like 'Good knowledge of New South Wales, find dining and extreme sports'. I want to create a way for people to search for writers based on their specialties. I want people to be able to enter a word or two (like "Australia", "food and beverage", "extreme sports", etc) in cell C6 then have a list of the names of any writers who have that text in their specialties in column H. I've seen a few examples of formulas I could use but I haven't quite been able to get them to work. Any help anyone can offer me would be fantastic. Thanks!

 

[Galgenstrick]

I'm a little confused. Where do you want the a names to appear when a value in C6 is entered?

 

[Rusty315]

Hi Galgenstrick I just realized that my explanation wasn't clear as I mentioned the range A3:H500 then talked about cell C6, as if they were on the same sheet. Cell C6 is on Sheet1, the array A3:H500 is on Sheet2. I would like the list to appear on Sheet 1 under C6, say from C8 down. Sorry for the confusion but thanks for looking at it for me! Cheers

 

[Galgenstrick]

That should be pretty easy if you don't care if the names are grouped together. But I'm not sure how to do it without vba if you want the names to appear consecutively down the C column. I'll have to get back to you on that one.

 

[Rusty315]

It's ok if the names don't appear consecutively because I can put the non-consecutive list on a hidden sheet then use an INDEX array formula to create a second list where they're all grouped consecutively. Other than having recorded a few simple macros, I'm not familiar with VBA but I'm open to using it if you have a solution.

 

[Galgenstrick]

Ok. Try adding this to Cell C8 and drag it down.

=IF(ISNUMBER(FIND($C$6,Sheet2!H3)),Sheet2!A3, "")



EDIT:

I just realized the above formula will be case sensitive for what is entered in cell C6. So try this one if you don't want it to be case sensitive.

=IF(ISNUMBER(FIND(LOWER($C$6),Sheet2!H3)),Sheet2!A3, "")

 

[Rusty315]

Excellent, works perfectly Galgenstrick! Thanks for your help, it's much appreciated!!