Wildcard Search Returning Multiple Values

[th3h0bb5]

Hello all,

I have taken a survey. All my responses are in Column A. I would like to be able to search through those responses and see if they contain a word. If it does, I want to return that cell. So, let's say my search cell (B2) is "bad". I'm looking for a formula to return every comment (cell value) in Column A that contains the word "bad".

I've done a good bit of searching and not found a way to successfully do this. I've attempted Vlookups, Index/Match, and other methods. Many do not function with wildcards, which makes this difficult. Any help would be greatly appreciated!

 

[seguin85]

I had a similar problem a while back, I changed my formula to hopefully help you. Try this out and see how it works.

=IFERROR(INDEX($A$5:$A$1000,SMALL(IF($A$5:$A$1000="*"&"bad"&"*",ROW($A$5:$A$100)-MIN(ROW($A$5:$A$1000))+1,""),ROW(1:1))),"")

 

[XOR LX]

I had a similar problem a while back, I changed my formula to hopefully help you. Try this out and see how it works.

=IFERROR(INDEX($A$5:$A$1000,SMALL(IF($A$5:$A$1000="*"&"bad"&"*",ROW($A$5:$A$100)-MIN(ROW($A$5:$A$1000))+1,""),ROW(1:1))),"")

Did you check your solution? You cannot use wildcards within a simple comparison like that.

Regards

 

[XOR LX]

With your search value (e.g. "bad") in B2, and assuming a search range of A2:A10, in D1:

=COUNTIF(A:A,"*"&B2&"*")

Then, in E1:

=IF(ROWS($1:1)>$D$1,"",INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$10)/ISNUMBER(SEARCH($B$2,A$2:A$10)),ROWS($1:1))))

Copy the formula in E1 down until you start to get blanks for the results.

If you don't have Excel 2010 or later, you'll need to change the formula in E1 to the following array formula**:

=IF(ROWS($1:1)>$D$1,"",INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($B$2,A$2:A$10)),ROW(A$2:A$10)),ROWS($1:1))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[seguin85]

No, I did not, when I did it for mine I didn't need wildcards. But I also forgot to mention it is an array formula. So you must ctrl+shift+enter the formula

 

[th3h0bb5]

That did the trick. Thanks so much! Finding a way to incorporate the wildcards was driving me crazy.

 

[th3h0bb5]

seguin85, I'm afraid it's not working for me, I think because of the wildcard I'm trying to use. Thanks anyways though.

 

[XOR LX]

You're welcome!

Cheers

 

[István Hirsch]

That did the trick. Thanks so much! Finding a way to incorporate the wildcards was driving me crazy.

Do you need each „bad” for example in badge, badminton, lambada etc.?

 

[th3h0bb5]

Do you need each „bad” for example in badge, badminton, lambada etc.?

I did, yes. The wildcard function that XOR LX gave me returned any cell containing "bad" anywhere in the text string.