Home |
Search |
Today's Posts |
#1
|
|||
|
|||
2 dimensional MATCH
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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 |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |