Would like to discuss a little bit about this function.
ROW(A1)-1 will always be zero. Why it is necessary to include this phrase?

The role of MIN function is to return a figure from an array of only this
figure and other "False". So is it possible to use MAX function, instead of
MIN? PLease give some hint in this regard.

"Domenic" wrote in message
...
Try...

=SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1)
+1)),MATCH(J2,INDEX(A1:C3,MIN(IF(A1:C3=J2,ROW(A1:C 3)-ROW(A1)+1)),0),0))),
"$","")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Actually, since
the table contains unique values, the following should suffice...

=SUBSTITUTE(CELL("address",INDEX(A1:C3,MIN(IF(A1:C 3=J2,ROW(A1:C3)-ROW(A1)
+1)),MIN(IF(A1:C3=J2,COLUMN(A1:C3)-COLUMN(A1)+1)))),"$","")

...which also needs to be confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
mpaino wrote:

Hi All,
is it possible to create a function that will look for a text/value in a
table and return the cell reference? I only have dierent values in the
table,
no value repeats.
For Example:

table A1:C3
aaa bbb ccc
abb abc acb
baa bba bbc

in another cell (J2) I have the value "abc" and I want a formula to
return
the cell reference in the spreadsheet nest to it. I don't want the "abc"
in
the formula because I want to find different values.

function(J2) returns the cell reference in the spreadsheet, B2.

Thanks a lot for the help