View Single Post
  #5   Report Post  
Alan Beban
 
Posts: n/a
Default

David wrote:
Greetings and TIA for your time.
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 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)

The form of the address(es) can be determined with a fourth argument, 1
thru 4, which operates the same as the 3rd argument to the built-in
ADDRESS function, i.e.,

1---- $A$1
2---- A$1
3---- $A1
4---- A1

The ArrayMatch function has a 5th argument for case matching, the
default value of which is False.

Alan Beban