Advanced Filter Wildcard using Formula

[pto160]

I'm able to use advanced filter with wildcards that are values only.
How do you use wildcard advanced filter using a formula.

Sheet3

	A	B
Column A
adfd dog g
dfdf green gfdf
sghwfh cat fdfd
fafad mouse fdfd
Criteria
Green
Mouse

<colgroup> <col style="WIDTH: 30px; FONT-WEIGHT: bold"> <col style="WIDTH: 87px"> <col style="WIDTH: 64px"></colgroup> <tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]FALSE
[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula 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]

Try this

Before filter


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Text​

[/td][td] [/td][/tr]


[tr][td]

2​

[/td][td]

adfd dog g​

[/td][td] [/td][/tr]


[tr][td]

3​

[/td][td]

dfdf green gfdf​

[/td][td] [/td][/tr]


[tr][td]

4​

[/td][td]

sghwfh cat fdfd​

[/td][td] [/td][/tr]


[tr][td]

5​

[/td][td]

fafad mouse fdfd​

[/td][td] [/td][/tr]


[tr][td]

6​

[/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td]

Criteria​

[/td][td]

Formula​

[/td][/tr]


[tr][td]

9​

[/td][td]

Green​

[/td][td]

FALSE​

[/td][/tr]


[tr][td]

10​

[/td][td]

Mouse​

[/td][td] [/td][/tr]
[/table]

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


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Text​

[/td][td] [/td][/tr]
[/tr]


[tr][td]

3​

[/td][td]

dfdf green gfdf​

[/td][td] [/td][/tr]
[/tr]


[tr][td]

5​

[/td][td]

fafad mouse fdfd​

[/td][td] [/td][/tr]


[tr][td]

6​

[/td][td] [/td][td] [/td][/tr]


[tr][td]

7​

[/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td]

Criteria​

[/td][td]

Formula​

[/td][/tr]


[tr][td]

9​

[/td][td]

Green​

[/td][td]

FALSE​

[/td][/tr]


[tr][td]

10​

[/td][td]

Mouse​

[/td][td] [/td][/tr]
[/table]

Hope this helps

M.

 

[pto160]

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.

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.