Vlookup with wildcards in the "table_array"

[Arul.rajesh]

Sorry if the title misguided you.


From a list of values in column "A" need to identify the values which begin with a certain string of characters listed here

CK
Credit Card
FRM
Xfer
Visa/Master
Transf
Trnsf


The data in column A looks like

AMERIHEALTH MANUAL P
AMERIHEALTH PAYMENT
CK 105
CK 1466
CK 2834
CK 323
CK 3255
CK 703
CK 7171
CK 828
CR CARD PAYMENT AMER
DELTA DENTAL
DUPLICATE POSTING
FRM ACCT 13470724
FRM ACCT 13485586
GHI PAYMENT
HEALTH PARTNERS
INCIDENTAL ONLY CHGS
MCAID GOVT PAYMENT M
ST CLOUD HLTHCARE CT
ST MARYS LIFE CENTER
TRANSFER FROM 259826
VISA/MASTER CHARGE
XFER FROM 260482430
XFER TO 8012197599



The ones beginning with the any of the strings listed above must be identified by a formula in column B.


This array formula works but is there a more elegant solution?

{=IF(SUM(N(IFERROR(SEARCH($E$1:$E$7,A1),0)))=1,"exclude","include")}


Assuming the first list of values is in $E$1:$E$7.

 

[Aladin Akyurek]

What are you planning to do items which starts with or contains values from $E$1:$E$7?

 

[pgc01]

Hi Arul

I think you have a good solution but this is another option:

=IF(SUM(COUNTIF(A1,$E$1:$E$7&"*")),"exclude","include")

confirmed with CSE

 

[Arul.rajesh]

I need to exclude them from the data I have. There are other columns in addition to the data in column A.

 

[Arul.rajesh]

Thanks pgc01

That eliminates some nested functions.

Non zero numbers evaluate to 1, keep forgetting to remember that.

 

[Aladin Akyurek]

I need to exclude them from the data I have. There are other columns in addition to the data in column A.

Do you want to run formulas on the included values or do you want to create a list somewhere else without the excluded values?

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

... Non zero numbers evaluate to 1 ...

Remark: I'd say they evaluate to True when casted to boolean.

 

[Arul.rajesh]

Do you want to run formulas on the included values or do you want to create a list somewhere else without the excluded values?

I want to create a list on another column in the same sheet say column B. then filter and remove the matches.

 

[pgc01]

I want to create a list on another column in the same sheet say column B. then filter and remove the matches.

Hi

In that case you can use the Advanced Filter with the option Copy to another location. It will create the list directly.

 

[Arul.rajesh]

Hi

In that case you can use the Advanced Filter with the option Copy to another location. It will create the list directly.

I have to identify the values in column "A" first, for which the formula is needed.

Is there a way in advanced filter to do pattern matching?