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

Biff

wrote in message
oups.com...
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