Search Function that Returns a cell name?

[Indra7]

Hi,
I need to have a function that will search a given worksheet
(or a range of cells on a given worksheet) for a string
and then report back the cell name where it was found.
In other words, if i want to look for the word "monkey" on a workshee
and "monkey" is in cell H83, I'd like it to return the value "H83".
It seems that something like this would already exist as a canne
function, but I can't seem to find it.

If someone could suggest how to do this within Excel or failing that
within an Excel VBA Macro, i would greatly appreciate it.

Thanks

 

[Dave Peterson]

This worked ok for me:

=ADDRESS(MAX((Sheet1!A1:C9="monkey")*ROW(Sheet1!A1:C9)),
MAX((Sheet1!A1:C9="monkey")*COLUMN(Sheet1!A1:C9)))

(all one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust that C9 to match the bottom right of what you want to search. (all 4
spots!)

 

[Alan Beban]

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, the
following will return the addresses of all the matches if array entered
into a column of cells sufficiently large to accommodate the number of
occurrences of the matches.

=ArrayMatch(soughtValue,A1:A6,"A",4)

The 4th argument controls whether the form of the addresses is $A$1,
A$1, $A1, or A1. Adding a fifth argument of True will make the function
Case Matching.

If there is only one occurrence of the sought value it need not be array
entered. If not enough cells are selected for the returned values, it
will return a message indicating the number of cells to be selected.

Alan Beban