2 dimensional MATCH

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from a 2
dimensional array.
 
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
 
This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/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/functions/sumproduct.htm


--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
 
David said:
Greetings and TIA for your time.
Is there any way of returning the (row, column) position of a value from a 2
dimensional array.
Click to expand...

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...
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})
Click to expand...

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)
Click to expand...
....

And without udfs, starting in F15, using

F15:
=IF(COUNTIF(Tbl,luv)>=ROW(A1),ADDRESS(
INT(SMALL((Tbl<>luv)*100000000+(ROW(Tbl)*100000+COLUMN(Tbl)),ROW(A1))/100000),
MOD(SMALL((Tbl<>luv)*100000000+(ROW(Tbl)*100000+COLUMN(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 said:
Alan Beban wrote...

from a 2
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})
Click to expand...


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)
Click to expand...

...

And without udfs, starting in F15, using

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

Fill F15 down as far as needed.
Click to expand...
 
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
 
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
Click to expand...
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.
Click to expand...
....

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Transposing from two dimensional to one dimensional 3
Inserting blanks or changing dimensions 1
two dimensional arrays 4
Match() worksheet function 10
vba array logic 3
Lookup for a word in a table 7
Filling excel entire row/column instead of single cell from an array 3
Help with multi-dimensional arrays 1

Back
Top