To answer your first question, you can use wildcards with COUNTIF, so
you can change the B2 reference to B2&"*" in both formulae.

Hope this helps.

Pete

On Aug 7, 4:28*am, Mea wrote:
I am trying to track 3 things, with the info from a linked file.
1. If the "name" I'm looking for is in the reference file. I'm using
=IF(COUNTIF('2009_Maps(1).xls'!southbed,B2)0,"sou th", " ")

2.The number of occurences of the name. I'm using
=COUNTIF('2009_Maps(1).xls'!southbed,B2)
Both these formula's work, provided that the name matches exactly. For
example, this will find "cadmium" but not "cadmium sp". How can I check for
cells that contain the partial name, as well as the full name?

3. I want to show the cell address for each occurence.
I can across a post from Ron that gave me this formula to find a cell
location.
=ADDRESS(SUMPRODUCT((A1:E10=G1)*ROW(A1:A10)),SUMPR ODUCT((A1:E10=G1)*COLUMN(*A1:E1)))

I have am using a range, and want nothing in the field if there are no
occurences, so I have used this.
=IFERROR(ADDRESS(SUMPRODUCT(('2009_Maps(1).xls'!so uthbed=B4)*ROW('2009_Maps*(1).xls'!southbed)),SUMP RODUCT(('2009_Maps(1).xls'!southbed=B4)*COLUMN('20 0*9_Maps(1).xls'!southbed)))," ")
However, this only works when there is one location. How can I have cell
address' for ALL locations show up. And have the answer, $CZ$613, show as
CZ613.

I'm in over my head, and the head of my resident Excel guru!, so I'm hoping
someone can help