Advanced Filter Wildcard using Formula

pto160

Active Member
Joined
Feb 1, 2009
Messages
303
Office Version
365
Platform
Windows
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,332
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
Office Version
365
Platform
Windows
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,084,748
Messages
5,379,616
Members
401,615
Latest member
syn_excel

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top