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?

Hi,
Take a look at the sumproduct function

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


"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?

1000x1000 array

That's 1 million cells. Are there any duplicates?

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
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?

Not likely, but if there are finding the first one would suffice. FYI I've
had to split up the array into 250x4000 to allow it to fit within the 256
column limit

"T. Valko" wrote:

1000x1000 array

That's 1 million cells. Are there any duplicates?

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
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?

With that many cells to check this will be *SLOW* plus it's volatile. You'd
probably be better off using a UDF.

This will return the cell address using duplicate precedence of top to
bottom, left to right.

Use a helper cell to get the max value:

tbl = your range

=MAX(tbl)

Assume that formula is entered in cell A1.

For the cell address for the max value...

Array entered** :

=CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1 ,ROW(tbl)-MIN(ROW(tbl))+1)),0),0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
Not likely, but if there are finding the first one would suffice. FYI I've
had to split up the array into 250x4000 to allow it to fit within the 256
column limit

"T. Valko" wrote:

1000x1000 array

That's 1 million cells. Are there any duplicates?

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
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?

Slight improvement.

Array entered:

=CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)))-MIN(ROW(tbl))+1,MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1,R OW(tbl)))-MIN(ROW(tbl))+1,0),0)))

That will make it a little bit more efficient.

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
With that many cells to check this will be *SLOW* plus it's volatile.
You'd probably be better off using a UDF.

This will return the cell address using duplicate precedence of top to
bottom, left to right.

Use a helper cell to get the max value:

tbl = your range

=MAX(tbl)

Assume that formula is entered in cell A1.

For the cell address for the max value...

Array entered** :

=CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1 ,ROW(tbl)-MIN(ROW(tbl))+1)),0),0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
Not likely, but if there are finding the first one would suffice. FYI
I've
had to split up the array into 250x4000 to allow it to fit within the 256
column limit

"T. Valko" wrote:

1000x1000 array

That's 1 million cells. Are there any duplicates?

--
Biff
Microsoft Excel MVP


"fireflyrt" wrote in message
...
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?

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?

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?

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?

(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?

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?