Searching Worksheets

P

paddys

Hi all,

I'm looking to modify my search of other worksheets. At the moment I am
currently using this formula which tells me the number of times that A6
is in the "Second Worksheet" in Column K.

=SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279)))

Now i want to modify it to instead of returning the number of times
that A6 is in Column K in the Second Worksheet, i want it to return the
cell references that it occurs in. So instead of returning 3 it will
return K70 K120 K200. I would even be happy if it just returned the row
where it was located.

Is this possible if so what functions should i be looking at?

Cheers,

Paddy
 
T

T. Valko

Are you using Search because A6 is a substring of what might be in 'Second
Worksheet'!$K$3:$K$279?

Biff
 
T

T. Valko

Try one of these. Both are array formulas and need to entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER):

This first one is based on your current formula. I'm assuming that by using
Search, A6 is a substring:

=IF(ROWS($1:1)<=COUNTIF(K$3:K$279,"*"&A$6&"*"),"K"&SMALL(IF(ISNUMBER(SEARCH(A$6,K$3:K$279)),ROW(K$3:K$279)),ROWS($1:1)),"")

If A6 is not a substring:

=IF(ROWS($1:1)<=COUNTIF(K$3:K$279,A$6),"K"&SMALL(IF(K$3:K$279=A$6,ROW(K$3:K$279)),ROWS($1:1)),"")

Copy down until you get blanks.

If A6 is not a substring:
=SUMPRODUCT(--ISNUMBER(SEARCH(A6,'Second Worksheet'!$K$3:$K$279)))
Click to expand...

You can just use:

=COUNTIF(K$3:K$279,A$6)

Or, you can still use Countif for substings (although I generally prefer the
Sumproduct formula):

=COUNTIF(K$3:K$279,"*"&A$6&"*")

Biff
 
Top