find

[Jack Sons]

In sheet1 D2 to D40 I have names, one in each cell. In sheet2 cells P332 to
P347 is text, all kinds of words, each cell also may or may not contain one
or more names (those of D2 to D40 in sheet1).

In cell E2 (sheet1) I want to put a formula that looks into P332 to P347 of
sheet2 and will put in E2 "x" if the name of D2 is found. I want to fill
down the formula to E40 so I will know of each name wether it is also in
P332 to P347 or not.

What should that formula be?

Jack Sons
The Netherlands

 

[Bob Phillips]

Jack,

Try this array formula

=IF(ISNUMBER(FIND(D2,Sheet2!$P$332:$P$347)),"x","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

One way:

Assuming the range P332347 in Sheet2 is named: MyList
(i.e. MyList: =Sheet2!$P$332:$P$347)

Put in E2 of Sheet1: =IF(ISNA(MATCH(TRIM(D2),MyList,0)),"","x")
Copy E2 down to E40

Alternatively,

Put in E2 of Sheet1:

=IF(ISNA(MATCH(TRIM(D2),MyList,0)),"","x - cell
P"&MATCH(TRIM(D2),MyList,0)+331)

which returns a text giving the cell ref of the matching name in MyList
in addition to the indicator "x"

Copy E2 down to E40

 

[mzehr]

Hi Jack!
Try
=IF(COUNTIF($p$332:$p$347,"*"&D2&"*")>0,"x","")

HTH

 

[mzehr]

Jack,
Ignore my array formula, and use Bob's instead, the
problem with mine is that if there is a partial match, ie
Sam and Samantha, Sam will be returned if Samantha is in
any of the cells, and Sam isn't. Sorry to lead you astray.

 

[Max]

Apologies, scratch my earlier post ..
Misread your specs for what's in Sheet2's P332347

Here's one way:

Assuming the range P332347 in Sheet2 is named: MyList
(i.e. MyList: =Sheet2!$P$332:$P$347)

Put in E2 of Sheet1:

=IF(ISBLANK(D2),"",IF(COUNTIF(MyList,"*"&D2&"*")>0,"x",""))

Copy E2 down to E40

 

[Jack Sons]

Bob,

I already used your formula (however without ISNUMBER) before posting my
question. To my chagrin the formula will work for one cell in sheet2 but not
for a range.
So if the name in D2 occurs in P338 of sheet2 the formula will result in the
desired "x" if I use it for that one cell

=IF(ISNUMBER(FIND(D2,Sheet2!$P$338)),"x","")

but not if the formula checks the whole range

=IF(ISNUMBER(FIND(D2,Sheet2!$P$332:$P$347)),"x","").

This gives blanks in all cells.

I don't understand why. Please shed light on it.

Jack.

 

[Jack Sons]

Max,

Thank you, it worked fine.

Jack.

 

[Frank Kabel]

Hi
for a range you may try
=IF(COUNTIF(Sheet2!$P$332:$P$347,"*x*")>0,"x","")

 

[Bob Phillips]

Jack,

As I said, it is an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Max]

Jack, you're welcome !
Glad it helped.