# If Contains then Macro

#### hmccullo

##### New Member
Hello,

I have a list of project names that I need to categorize into customers. I want to essentially be able to a vlookup, but instead of the vlookup being based on the entire cell value I want it to be able to recognize if it contains specific words. For instance if the data set was:

McDonalds NewYork
Texas McDonalds Houston
Atlanta McDonalds

I want to be able to have all of these project names attached to Mcdonald's in column B. Ideally I'd like to be able to have a list that has all of the key words I'm searching for and in the cell next to it the customer its associated with. Not all of them will be like for like meaning for Mcdonalds I will need to search for Mcdonalds, McDonald's, McD, etc.

Any ideas?

Thanks so much!

### 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.
hmcculo,
Not sure if this is what you are looking for... There are a lot of ways to get the data that only contains "McDonalds". One way is a formulaic solution to showing all fields that contain just your requested data.

Column A contains your list of projects
Column B is left blank (just out of habit to make it a data set)
Cell D2 is the variable. When you change this (for instance to "McDonalds") the formula data changes.
C5:C10 contain the following formula: =IFERROR(INDEX(\$A\$1:\$A\$6,AGGREGATE(15,6,(ROW(\$A\$1:\$A\$6)/NOT(ISERR(SEARCH(\$D\$2,\$A\$1:\$A\$6)))),ROWS(C5:C\$5))),"")
That formula is entered in C5 and dragged down to C10.

The result looks like this:
 A: B: C: D: Project Name: McDonalds NewYork Project: Burger King Burger King Philadelphia Texas McDonalds Houston Contains Project: Atlanta McDonalds Burger King Philadelphia New York Burger King New York Burger King

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

<tbody>
</tbody>
You could also use the advanced filter to generate this data very quickly.

If this is not what you are looking for, please provide more detail.

Replies
11
Views
251
Replies
2
Views
279
Replies
9
Views
287
Replies
2
Views
140
Replies
0
Views
255

1,214,367
Messages
6,119,117
Members
448,870
Latest member
max_pedreira

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