# Concatenate and Wildcards Problem

#### dickpierce

##### Board Regular
Hello!

I've concatenated some cells in order to use VLOOKUP. So for example, I concatenated Oklahoma with Alabama which gives me OklahomaAlabama so when I use VLOOKUP I get the desired result. However, I'm getting results from cells with data such as OklahomaSouth Alabama which I don't need. I'm using the following wildcard: "*"&"Alabama" but it's not excluding the South part.

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Kerryx

##### Well-known Member
Which looks correct as the wildcard is searching for anything containing "Alabama"

#### dickpierce

##### Board Regular
Unfortunately I don't want South Alabama, so how can I exclude it?

#### Marcelo Branco

##### MrExcel MVP
Hard to help without seeing your data.
Try to show us a small data sample (5 - 10 rows) along with lookup table and expected results.

M.

Last edited:

#### dickpierce

##### Board Regular
Here it is: Unfortunately it's including South Alabama

A B C D E

2 Oklahoma 19 =If(a1="","",b2&b1) =iferror(vlookup("*"&"Alabama",d2:d2,1,false),"")
3 Alabama 41
4 Wisconsin 12
5 Ohio St 35
6 Auburn 38
7 Georgia 14
8 Washington 28
9 Penn St 31
10 South Alabama 9
11 Clemson 31

#### dickpierce

##### Board Regular
Here it is: Unfortunately it's including South Alabama
Bumped in order to clarify

A B C D E

2 Oklahoma 19 =If(a1="","",b2&b1) =iferror(vlookup("*"&"Alabama",d2:d2,1,false),"")
3 Alabama 41
4 Wisconsin 12
5 Ohio St 35
6 Auburn 38
7 Georgia 14
8 Washington 28
9 Penn St 31
10 South Alabama 9
11 Clemson 31

#### pgc01

##### MrExcel MVP
Hi

Why are you using the wildcard in

"*"&"Alabama"

?

Last edited:

#### dickpierce

##### Board Regular
There is a lot of data that includes the word Alabama, so I'm using VLOOKUP to get a result from concatenated data like OhioAlabama and IowaAlabama, unfortunately I'm also getting results that I don't need from words like MaineSouth Alabama. South Alabama is what's gumming up my results. Hopefully I'm clear?

#### pgc01

##### MrExcel MVP
Sorry, no, it's not clear.
I understand your example (I think) but not the whole picture.

If I understand your example correctly, you want to find text that contains "Alabama" and does not contain "South").

If that's the case, for ex., in B2:

=MATCH(1,INDEX(ISNUMBER(SEARCH(A2,E2:E12))*ISERROR(SEARCH("South",E2:E12)),),0)

#### dickpierce

##### Board Regular
Awesome PGC!! That's it! Works great thanks so much!

Replies
2
Views
274
Replies
4
Views
179
Replies
1
Views
125
Replies
5
Views
374
Replies
3
Views
87

1,196,018
Messages
6,012,890
Members
441,738
Latest member
dataexcel

### 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.

### Which adblocker are you using?

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

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