Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from a 2
dimensional array.
--
David

check this thread. Is this what you want...?

http://excelforum.com/showthread.php?t=369480

- Mangesh


"David" wrote in message
...
Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from a
2
dimensional array.
--
David

Mangesh,

Your solution

=INDEX(L1:L10,SUMPRODUCT(--($A$1=B1:K10),(IF(B1:K10<$A$1,0,ROW(B1:K10)))))

works fine, but only if the numbers in the matrix occur not more than once.

Is it possible to return a list {.......} of all values of L1 to L10 (or -
preferably - in a column by copying the formula down) corresponding to the
rows in which the reference value (in this case the 123 in A1) occurs,
allowing for more than one occurence per row and also allowing for more than
one per column?

Jack Sons
The Netherlands




"Mangesh Yadav" schreef in bericht
...
check this thread. Is this what you want...?

http://excelforum.com/showthread.php?t=369480

- Mangesh


"David" wrote in message
...
Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from
a
2
dimensional array.
--
David

This file might be a help:
http://www.bygsoftware.com/examples/.../xindexvba.zip
It's in the "Excel for Lotus 123 Users" section on page:
http://www.bygsoftware.com/examples/examples.htm

In Excel there is no direct equivalent for Lotus 123's XINDEX function. This
workbook shows two Excel formula constructions that achieve the same result.

The first example uses two additional inputs. It uses the Excel functions:
INDEX and MATCH The second example uses the same inputs as the Lotus XINDEX
function. It uses the Excel functions: INDEX, MATCH and OFFSET.

There are also two additional pieces of VBA showing how to use this in code
and capture an error condition.

The code is open and commented.

There is also an alternative construction using SUMPRODUCT at:
http://www.bygsoftware.com/Excel/fun...sumproduct.htm


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"David" wrote in message
...
Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from a
2
dimensional array.
--
David

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

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.

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.

Hi Jack Sons,

maybe something like this...

=IF(SUMPRODUCT(--($A$1=B1:K1),(IF(B1:K1<$A$1,0,ROW(B1:K1))))=0,"", L1)

drag down formula

Mangesh



"Jack Sons" wrote in message
...
Mangesh,

Your solution


=INDEX(L1:L10,SUMPRODUCT(--($A$1=B1:K10),(IF(B1:K10<$A$1,0,ROW(B1:K10)))))

works fine, but only if the numbers in the matrix occur not more than
once.

Is it possible to return a list {.......} of all values of L1 to L10 (or -
preferably - in a column by copying the formula down) corresponding to the
rows in which the reference value (in this case the 123 in A1) occurs,
allowing for more than one occurence per row and also allowing for more
than
one per column?

Jack Sons
The Netherlands




"Mangesh Yadav" schreef in bericht
...
check this thread. Is this what you want...?

http://excelforum.com/showthread.php?t=369480

- Mangesh


"David" wrote in message
...
Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value
from
a
2
dimensional array.
--
David

Alan Beban wrote...
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.
....

Good point. Subtract (CELL("Row",Tbl)-1) from the first and
(CELL("Col",Tbl)-1) from the second to get row and column indices,
respectively, within the array.