In article ,
"Rasoul Khoshravan" wrote:

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?

This part of the formula...

MIN(IF(A1:C3=J2,ROW(A1:C3)-ROW(A1)+1))

....returns the row number in which the criteria is found, relative to
the first row of the reference.

In this case, because the data starts in Row 1, this part -ROW(A1)+1
isn't needed. But it's needed if the data starts in a row other than
the first one, or if one or more rows are inserted at row one.

For example, if A2:C4 contains the data, ROW(A2:C4) returns the
following array of numbers...

2
3
4

If ROW(A2:C4)-ROW(A2)+1 is used instead, the following array of numbers
is returned...

1
2
3

So as you can see, the added part at the end is used to return an array
of numbers starting with the number 1.

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.

If the data contains unique values, then it doesn't matter which one is
used. However, if Row 1 and Row 3 both contain the criteria, MIN will
return Row 1, whereas MAX will return Row 3.

Hope this helps!

In article ,
"Rasoul Khoshravan" wrote:

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