Vlookup with wildcards in the "table_array"

Arul.rajesh

Active Member
Joined
Sep 20, 2011
Messages
285
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.
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
What are you planning to do items which starts with or contains values from $E$1:$E$7?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

Active Member
Joined
Sep 20, 2011
Messages
285
I need to exclude them from the data I have. There are other columns in addition to the data in column A.
 

Arul.rajesh

Active Member
Joined
Sep 20, 2011
Messages
285

ADVERTISEMENT

Thanks pgc01

That eliminates some nested functions.

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881

ADVERTISEMENT

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

Active Member
Joined
Sep 20, 2011
Messages
285
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
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

Active Member
Joined
Sep 20, 2011
Messages
285
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,483
Messages
5,596,405
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top