Actually, that shouldn't be the case. Did the formula return an
incorrect value?

In article ,
mpaino wrote:

It works very well, thanks a lot for the help, but in the first formula I
figured out by the second that the last "row" has to be replaced by "column".

"Domenic" wrote:

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