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,308
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,078,520
Messages
5,340,922
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top