Advanced Filter Wildcard using Formula

pto160

Active Member
Joined
Feb 1, 2009
Messages
303
I'm able to use advanced filter with wildcards that are values only.
How do you use wildcard advanced filter using a formula.

Sheet3

AB
1Column A
2adfd dog g
3dfdf green gfdf
4sghwfh cat fdfd
5fafad mouse fdfd
6
7
8 Criteria
9GreenFALSE
10Mouse

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 87px"> <col style="WIDTH: 64px"></colgroup> <tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B9=ISNUMBER(MATCH("*"&A2&"*",$A$9:$A$10,0))

<tbody>
</tbody>

<tbody>
</tbody>


This formula does not work for looking up values using wildcards.

What's wrong with the formula?

 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
Try this

Before filter


A
B
1
Text​
2
adfd dog g​
3
dfdf green gfdf​
4
sghwfh cat fdfd​
5
fafad mouse fdfd​
6
7
8
Criteria​
Formula​
9
Green​
FALSE​
10
Mouse​

Formula in B9
=ISNUMBER(LOOKUP(9.99E+307,SEARCH($A$9:$A$10,A2)))

Apply Advanced Filter
List range: $A$1:$A$5
Criteria range:$B$8:$B$9

After filter


A
B
1
Text​
3
dfdf green gfdf​
5
fafad mouse fdfd​
6
7
8
Criteria​
Formula​
9
Green​
FALSE​
10
Mouse​

[/tr]



[/tr]



Hope this helps

M.
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
303
Fantastic. This really helps. I spent a few hours today trying to figure this out. This will save me a lot of time at work. Thank you. :)
 

Forum statistics

Threads
1,077,918
Messages
5,337,167
Members
399,130
Latest member
josevidal

Some videos you may like

This Week's Hot Topics

Top