1. ### Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. Hi All

Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

Post Spam and you Will Be Deleted as a User

Hui...

3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

# How to return all rows with matching wildcard criteria

Discussion in 'Ask an Excel Question' started by Finny99, Apr 19, 2017.

1. ### Finny99New Member

Messages:
5
I've been trying to get a formula to return all rows from a tab in which multiple criteria are met, including a wildcard criteria. I've attached the sample excel in terms of what I'm trying to achieve. It's very basic and not the actual dataset, but you get the idea. I can get it when the cells are exact matches, but not if the cell contains it.

Please help if you can and thanks.

File size:
9.6 KB
Views:
11
2. ### AlanSidmanActive Member

Messages:
164
try this VBA solution:
Code (vb):

Option Explicit

Sub FindText()
Dim i As Long, lr As Long, lr2 As Long
lr = Range("A" & Rows.Count).End(xlUp).Row
Dim sFind As String
sFind = Range("G2")
Application.ScreenUpdating = False
For i = 2 To lr
lr2 = Range("F" & Rows.Count).End(xlUp).Row
If InStr(1, Range("B" & i), sFind) > 0 Then
Range("A" & i & ":C" & i).Copy Range("F" & lr2 + 1)
End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
3. ### PaulFActive Member

Messages:
203
I hope this helps you,

I added a helper column with: =IF(AND(ISNUMBER(SEARCH(\$F\$2,A2)),ISNUMBER(SEARCH(\$G\$2,B2))),MAX(\$D\$1:D1)+1,"")

Then a lookup value built on an expanding array: =INDEX(A\$2:A\$5,MATCH(ROWS(\$F\$6:F6),\$D\$2:\$D\$5,0))

See attached.

Respectfully,
PaulF

File size:
10.2 KB
Views:
4
4. ### NebuExcel Ninja

Messages:
2,047
Hi:

Use the following formula.

=INDEX(\$A\$2:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$5)-MIN((\$A\$2:\$A\$5))-1)/MMULT((\$F\$2=\$A\$2:\$A\$5)*(IF(ISNUMBER(SEARCH("*"&\$G\$2,\$B\$2:\$B\$5))>0,1)),1),ROW(A1)))

Thanks

#### Attached Files:

• ###### Return Multiple Rows Excel.xlsx
File size:
10.2 KB
Views:
7
PaulF likes this.
5. ### PaulFActive Member

Messages:
203
I need to save this and rip it apart to learn and reply "I'm not worthy... "
6. ### NebuExcel Ninja

Messages:
2,047
Hi:

A small Correction

=INDEX(\$A\$2:\$A\$5,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$5)-MIN((\$A\$2:\$A\$5))-1)/MMULT((\$F\$2=\$A\$2:\$A\$5)*(ISNUMBER(SEARCH("*"&\$G\$2,\$B\$2:\$B\$5))),1),ROW(A1)))

Yon can take out the If condition from the Isnumber/ search condition.

Thanks
7. ### Finny99New Member

Messages:
5
Thanks everyone for the suggestions. I'll give them a shot. You all rock.
8. ### Finny99New Member

Messages:
5
how do I enter this formula? I was trying to edit it and then drag the formula across, but it doesn't return the same values as are in the cells. Sorry for the stupid question. Also, I switched the selection from New to Old and it returned both of the rows for OLD but it shouldnt' do that.
9. ### NebuExcel Ninja

Messages:
2,047
Hi:

It works fine at my end here the file where I switched the selection from New to Old and it returned only one row for OLD.

Thanks

File size:
10.2 KB
Views:
9
10. ### Finny99New Member

Messages:
5
How do I manage this formula? I try dragging across to more cells and it ceases to work properly. Is there something special?
11. ### Finny99New Member

Messages:
5
nevermind. I noticed the formula had locked cells in the first piece which weren't moving when being dragged. THanks again.
12. ### bosco_yipWell-Known Member

Messages:
1,248
In addition to remove the "\$" sign near INDEX, MMULT function and "*" also can take out.

and, become >>

=INDEX(A\$2:A\$5,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$5)-MIN(\$A\$2:\$A\$5)-1)/(\$F\$2=\$A\$2:\$A\$5)*ISNUMBER(SEARCH(\$G\$2,\$B\$2:\$B\$5)),ROW(A1)))

Regards
13. ### NebuExcel Ninja

Messages:
2,047
Hi:

Its ok to take out "*", but if you take out MMULT , the order in which the Output are shown will be in reverse.

Thanks
14. ### bosco_yipWell-Known Member

Messages:
1,248
Hi,

1] The output reverse problem can be fixed by changing a "*" into "/" as in :

=IFERROR(INDEX(A\$2:A\$5,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$5)-MIN(\$A\$2:\$A\$5)-1)/(\$F\$2=\$A\$2:\$A\$5)/ISNUMBER(SEARCH(\$G\$2,\$B\$2:\$B\$5)),ROW(A1))),"")

2] A comparison formula file is herein attached

Regards

#### Attached Files:

• ###### Return Multiple Rows Excel(1).xlsx
File size:
11.3 KB
Views:
10
Thomas Kuriakose likes this.