Finding a match within a cell using an index (array) of strings

N

Nekto

New Member
Joined
Nov 18, 2008
Messages
18
Guys - could you please help me with the following problem..

I have a thousand of entries in a column. Each entry contains many different entries all separated by a semicolon. Suppose it's the state names, i.e. one cell has Washington; California; Nevada -- and another cell has Illinois; Indiana; Alaska; Arizona.

What I need is to search within each of these 1000 entries (just fill the formula down), but want to search a set number of states (suppose I define Florida, Arizona, Nevada).

So if the entry has one of these states, return a 1, if it doesn't have these states, return a zero.

What complicates this is that it's basically looking up a string within a set of entries. I could try "if(isnumber(find(index of states, entry)), 1, 0)", but it's not working... help?

would be greatly appreciated, thanks..
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Nekto said:
Guys - could you please help me with the following problem..

I have a thousand of entries in a column. Each entry contains many different entries all separated by a semicolon. Suppose it's the state names, i.e. one cell has Washington; California; Nevada -- and another cell has Illinois; Indiana; Alaska; Arizona.

What I need is to search within each of these 1000 entries (just fill the formula down), but want to search a set number of states (suppose I define Florida, Arizona, Nevada).

So if the entry has one of these states, return a 1, if it doesn't have these states, return a zero.

What complicates this is that it's basically looking up a string within a set of entries. I could try "if(isnumber(find(index of states, entry)), 1, 0)", but it's not working... help?

would be greatly appreciated, thanks..
Click to expand...
Try this...

List the names to be searched for in a range of cells:
  • G2 = Florida
  • G3 = Arizona
  • G4 = Nevada
A2 = Washington; California; Nevada

Enter this formula in B2:

=--(SUMPRODUCT(--(ISNUMBER(SEARCH(G$2:G$4,A2))))>0)
 
Upvote 0
It sounds like FIND would work well. You could use the following in B1 and copy it down:

=IF(OR(FIND("Florida",A1)>0,FIND("Arizona",A1)>0,FIND("Nevada",A1)>0),1,0)
 
Upvote 0
Are you sure you just want to return a 1 if there's a match,0 if not?
Do you want to go a step further, and return which state it found?


This will give 1/0 results
F2:F4 holds list of States to look for.
A2 holds the long string containing multiple states.

=--(ISNUMBER(LOOKUP(2^15,SEARCH($F$2:$F$4,A2))))


If you want to go further, and return which state it found in the string...

=LOOKUP(2^15,SEARCH($F$2:$F$4,A2),$F$2:$F$4)

But it will return #N/A error when there is no match..
 
Upvote 0
Nekto said:
Guys - could you please help me with the following problem..

I have a thousand of entries in a column. Each entry contains many different entries all separated by a semicolon. Suppose it's the state names, i.e. one cell has Washington; California; Nevada -- and another cell has Illinois; Indiana; Alaska; Arizona.

What I need is to search within each of these 1000 entries (just fill the formula down), but want to search a set number of states (suppose I define Florida, Arizona, Nevada).

So if the entry has one of these states, return a 1, if it doesn't have these states, return a zero.

What complicates this is that it's basically looking up a string within a set of entries. I could try "if(isnumber(find(index of states, entry)), 1, 0)", but it's not working... help?

would be greatly appreciated, thanks..
Click to expand...

Create a range with:

Florida
Arizona
Nevada

in say X2:X4. Select this range and name the selection SearchList
using the Name Box on the Formula bar.

Let column A2 from A2 on house the data that we want to evaluate...

In B2 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEACH(SearchList,A2)))+0
 
Upvote 0
T. Valko said:
Try this...

List the names to be searched for in a range of cells:
  • G2 = Florida
  • G3 = Arizona
  • G4 = Nevada
A2 = Washington; California; Nevada

Enter this formula in B2:

=--(SUMPRODUCT(--(ISNUMBER(SEARCH(G$2:G$4,A2))))>0)
Click to expand...


Nice, this works (I guess SEARCH and FIND are identical formulas). Didn't know you could just set a range within the search function.

Thank you (and others) for your help. The nesting IFs unfortunately wouldn't work as I might have to expand the list of searchable states to more than 4 (maybe eventually 20).
 
Upvote 0
Nekto said:
Nice, this works (I guess SEARCH and FIND are identical formulas). Didn't know you could just set a range within the search function.

Thank you (and others) for your help. The nesting IFs unfortunately wouldn't work as I might have to expand the list of searchable states to more than 4 (maybe eventually 20).
Click to expand...
You're welcome. Thanks for the feedback!

SEARCH and FIND work the same *except* that FIND is case sensitive while SEARCH is not.

For eaxmple:

A1 = XX

SEARCH("xx",A1) = 1
FIND("xx",A1) = #VALUE!
FIND("XX",A1) = 1
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
How to extract matching data to rows using formula
Replies
3
Views
176
Peter_SSs
Peter_SSs
C
  • Solved
Index and match with result average
Replies
8
Views
3K
dreid1011
dreid1011
Ashe
  • Solved
Help matching multiple permissions in columns
Replies
6
Views
503
Fluff
Fluff
L
  • Solved
VBA Find Cell from Date listed, in Columns, Return cell to highlight in color
Replies
7
Views
528
mumps
mumps
T
  • Solved
Search for Cells of certain color and empty
Replies
3
Views
363
TheTiredEngineer
T

Forum statistics

Threads
1,224,507
Messages
6,179,181
Members
452,893
Latest member
denay

Share this page

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
Back
Top