Biff is correct (as usual!) this will not work in his example above.

If the OP just wants to identify a cell containing a value,
one could make use of the INDIRECT formula by giving it a name.
eg define "M" to be:

=INDIRECT(TEXT(MIN(IF($A$1:$H$9=MAX($A$1:$H$9),100 0*ROW($A$1:$H$9)+COLUMN($A$1:$H$9))),"r0c000"),0)

Then just enter "M" in the Name Box (next to formula bar) to go
to the first cell containing the Max value.

You could also enter the formula directly into the EditGoto box
(without the leading =).

"Jacob Skaria" wrote:

You are right. Again...(as more often)...I have missed the key point
here..The below will do...

=ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Not sure whether you have read the previous post...

I did, but what does it have to do with what the OP is wanting to do?

The OP wants the cell address of the max value in the range. There may or
may not be duplicate max values. So, if there are duplicate max values then
you have to decide which max value appears first:

......A.....B
1...5.....7
2...7.....3

Which max value appears first?


--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Not sure whether you have read the previous post...

------------

"No TV. I intended to put INDIRECT() for the below reason..

To retrieve a value from the same row from subsequent columns; the formula
will have to be adjusted like the below...It is true that I have made an
assumption that the OP is reasonably well with formulas (from the
query)...and hence this way.."

(all in one line)
=INDIRECT(ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment))

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

One of us is "out to lunch"! <g

......A.....B.....C
1...z......y......g
2...o.....x......r

=INDIRECT(ADDRESS(
MIN(IF(A1:C2="x",ROW(A1:C2),"")),
MIN(IF(A1:C2="x",COLUMN(A1:C2),""))))

Result = x

What did that formula accomplish?

If just want to know if "x" is present then use COUNTIF.


--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
(Correction) To retrieve value from the next column to the right

=INDIRECT(ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1))


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

No TV. I intended to put INDIRECT() for the below reason..

To retrieve a value from the same row from subsequent columns; the
formula
will have to be adjusted like the below...It is true that I have made
an
assumption that the OP is reasonably well with formulas (from the
query)...and hence this way..

(all in one line)
=INDIRECT(ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column
increment)


If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

Enter a text 'findme' somewhere in the range
=INDIRECT(ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) ))

That doesn't make any sense. What's the point of telling the formula
to
find
something and then having the result of the formula being what it is
you're
trying to find? You probalby didn't mean to include INDIRECT.

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in
message
...
Try the below. Enter a text 'findme' somewhere in the range
A1:J20.
and
try
the below array formula..which will retrive the value..Adjust to
suit...your
requirement

(all in one line)
=INDIRECT(ADDRESS(
MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")),
MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) ))

If this post helps click Yes
---------------
Jacob Skaria


"fireflyrt" wrote:

The Match function seems to work only on 1 dimensional arrays. Is
there
any
such function that returns the cell reference for a match in a 2d
array?

For example, I am trying to find the cell location of a max
number
in a
1000x1000 array of data - is there a function that will do that?