Interested users, if any, should be aware that the formulas that Harlan
Grove and I posted respond to two different problems. The formula I
provided =INDEX(ArrayMatch(soughtValue,Tbl4),ROW(A1),{1,2}) ,
returns, as I stated, the row and column numbers *within the array* of
all occurrences of the sought value.

The first two formulas that Harlan Grove provided, return the row and
column numbers *within the worksheet* of all those occurrences.

One way to modify the formula I posted, in order to get the row and
column numbers *within the worksheet* is to array enter into two
adjacent cells (e.g., B15 and C15) the following formulas, respectively,
and fill down:

B15:
=ROW(INDIRECT(INDEX(ArrayMatch(soughtValue,Tbl4,"A "),ROW(A1),1)))

C15: =COLUMN(INDIRECT(INDEX(ArrayMatch(soughtValue,Tbl4 ,"A"),ROW(A1),1)))

Alan Beban

Harlan Grove wrote:
Alan Beban wrote...

David wrote:

Is there any way of returning the (row, column) position of a value

from a 2

dimensional array.

If the functions in the freely downloadable file at
http:/home.pacbell.net/beban are available to your workbook, the
following, array entered into a two-cell row and filled down, will
return the row and column numbers, within the array, of all

occurrences

of the sought value in a range named Tbl4:

=INDEX(ArrayMatch(soughtValue,Tbl4),ROW(A1),{1,2 })


And without udfs, starting in cell B15, using

B15 [array formula]:
=IF(COUNTIF(Tbl,luv)=ROW(A1),INT(SMALL((Tbl<luv) *100000000
+(ROW(Tbl)*100000+COLUMN(Tbl)),ROW(A1))/100000),"")

C15:
=IF(B15<"",MOD(SMALL((Tbl<luv)*100000000
+(ROW(Tbl)*100000+COLUMN(Tbl)),ROW(A1)),100000),"" )

where Tbl is the table and luv the lookup value. Fill B15:C15 down as
needed.


And the following, entered in a cell and filled down, will return the
worksheet addresses, in absolute form, of those occurrences:

=INDEX(ArrayMatch(soughtValue,Tbl4,"A"),ROW(A1), 1)

...

And without udfs, starting in F15, using

F15:
=IF(COUNTIF(Tbl,luv)=ROW(A1),ADDRESS(
INT(SMALL((Tbl<luv)*100000000+(ROW(Tbl)*100000+CO LUMN(Tbl)),ROW(A1))/100000),
MOD(SMALL((Tbl<luv)*100000000+(ROW(Tbl)*100000+CO LUMN(Tbl)),ROW(A1)),100000),
4),"")

Fill F15 down as far as needed.