Help with searching a column

P

paddys

I am having some trouble searching an entire column to see if a value
is in the column. I've tried
=IF(ISNUMBER(SEARCH(A1,K:K)),"TRUE","FALSE") - But it doesn't seem to
be working. It only works if the A1 is in every cell of K.

I have two columns, the first I have a list of values each on its own
row.
column A
1. CSS-PB-350
2. CSS-PB-122
3. CSS-PB-100

The second contains multiple values in each cell.
Column B
1. CSS-PB-350, CSS-PB-111
2. CSS-PB-100, CSS-PB-091
3. CSS-PB-101, CSS-PB-021, CS-PB-234

So I would want to find that CSS-PB-350, CSS-PB-100 is in column B and
CSS-PB-122 is not.

Thanks

Paddy
 
M

Max

One way to get going ..

Source data in cols A and B, from row1 down
with items in col A to be searched against those in col B
(Col B's list assumed to be within B1:B100)

Placed in say, C1:
=IF(A1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(A1,$B$1:$B$100)))>0,"TRUE","FALSE"))
Copy C1 down to last row of data in col A
 
M

Max

Extending the earlier suggestion for other possible end-objectives ..

Instead of just returning TRUE/FALSE in col C, suppose we want to strip out
a list of items in col A found in col B, then we could place:

In C1 (slightly revised):
=IF(A1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(A1,$B$1:$B$100)))>0,ROW(),""))

In D1:
=IF(ROW()>COUNT(C:C),"",INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0)))

Then just select C1:D1 and copy down to the last row of data in col A. Col D
will return the list of items in col A found in col B, all neatly bunched at
the top.

The converse stripping -- ie listing items in col A not found in col B --
can be easily achieved by using instead in C1, and copying down (just a small
tweak):
=IF(A1="","",IF(SUMPRODUCT(--ISNUMBER(SEARCH(A1,$B$1:$B$100)))>0,"",ROW()))

No change is needed to the extract formulas within col D. Col D will now
list items in col A not found in col B, again with all results neatly bunched
at the top.
 
M

Max

For all of the foregoing, replace SEARCH with FIND if you need the search to
be case sensitive. SEARCH is not case sensitive.
 
Top