#### dasher108

##### New Member
OK so here I am again looking for help. (I really do not have alot of posts because I normally figure a solution by searching the forum)

The scenario

Here is what I started doing and then thought vba must be better

=IF(ISNUMBER(SEARCH("Red",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("Ron",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("cat1",Data1!B2)),"P1",IF(ISNUMBER(SEARCH("air",Data1!B2)),"P2",IF(ISNUMBER(SEARCH("Cog",Data1!B2)),"P2"))))))+IF(ISNUMBER(SEARCH("Sul",Data1!B2)),"P2")

Thank you for any help
Lee

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### njimack

##### Well-known Member
=IF(ISNUMBER(MATCH({"Red";"Ron";"cat";"cat1";"air";"Cog";"Sul"},Data1!B2,0)),"P1","")

EDIT - it may be better to have the list of values you're searching for in a range (say A1:A7). Your formula would then be
=IF(ISNUMBER(MATCH(A1:A7,Data1!B2,0)),"P1","")

#### dasher108

##### New Member
=IF(ISNUMBER(MATCH({"Red";"Ron";"cat";"cat1";"air";"Cog";"Sul"},Data1!B2,0)),"P1","")

Looks simple, did not even think of that I will give it a try. Is there a limit to how many conditions I can use?

#### njimack

##### Well-known Member
Looks simple, did not even think of that I will give it a try. Is there a limit to how many conditions I can use?

Don't think so, but if you have a long list, consider using a range of cells, per my edited post.

#### texasalynn

##### Well-known Member
so here is a case statement

Code:
``````Select Case Range("B2").Value
Case "Red", "Ron", "cat", "cat1"
Range("C2") = "P1"
Case "air", "Cog", "Sul"
Range("C2") = "P2"
End Select``````

#### dasher108

##### New Member
Don't think so, but if you have a long list, consider using a range of cells, per my edited post.

The list of text referenced on Data1 is populated automatically from a different program. I do not think I will be able to create a range I can choose from.

I really thing vba case select would work well here. I am just having trouble finding reference posts.

Thank you
Lee

#### texasalynn

##### Well-known Member
did you look at my post?

#### dasher108

##### New Member
I did thank you! You must have just beat me to the response.

#### dasher108

##### New Member
Ok I am really struggling with this.

A different program exports unformatted data to "Data 1" data sheet. I am using a different worksheet in the same work book to display the data in a format I like.

Might be easier if the attached is viewed

#### njimack

##### Well-known Member
What you've posted bears no relation to your original question. It looks like all that's required is a simple MATCH function.

Excel Workbook
ABCDEFGH
5IDRANKLOCATIONDATEDESCRIPTION**List of Locations
66322591North07-Martext description**North
76322912South06-Martext description**South
86322983East06-Martext description**East
96323044West07-Martext description**West
106323055North West07-Martext description**North West
116323076South East07-Martext description**South East
126323091North07-Martext description***
previous

Replies
3
Views
291
Replies
1
Views
747
Replies
0
Views
304
Replies
5
Views
492
Replies
1
Views
1K

1,191,118
Messages
5,984,748
Members
439,907
Latest member
Kayfabe

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