| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match() worksheet function
Excel Help indicates that the Match() function works for an Array, but I can
only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match() worksheet function
MATCH...
The lookup_array must be a one dimensional array. -- Biff Microsoft Excel MVP "WCM" wrote in message ... Excel Help indicates that the Match() function works for an Array, but I can only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match() worksheet function
Then is there a best way to find location of a single literal value (or
referenced value) in a two-dimensional array? Say I have alpha characters in the Excel range A1:M9. I want to find the first location of "G" in that array. Is there any function that would return say (5, 7) - i.e, for location of "G" in row-5, col-7? "T. Valko" wrote: MATCH... The lookup_array must be a one dimensional array. -- Biff Microsoft Excel MVP "WCM" wrote in message ... Excel Help indicates that the Match() function works for an Array, but I can only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match() worksheet function
It depends on what you consider the "first" location:
...........A..........B 1........X.........Y 2........Y.........X What is the first location of Y ? Is it B1 or A2 ? -- Biff Microsoft Excel MVP "WCM" wrote in message ... Then is there a best way to find location of a single literal value (or referenced value) in a two-dimensional array? Say I have alpha characters in the Excel range A1:M9. I want to find the first location of "G" in that array. Is there any function that would return say (5, 7) - i.e, for location of "G" in row-5, col-7? "T. Valko" wrote: MATCH... The lookup_array must be a one dimensional array. -- Biff Microsoft Excel MVP "WCM" wrote in message ... Excel Help indicates that the Match() function works for an Array, but I can only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match() worksheet function
That would depend on how excel does the search - whichever "Y" it runs into
first. For my purpose, any cell address with the value "Y" would work. Specifically, what I need is the row number of any "Y" in the A1:M9 array - doesn't matter which "Y". I will use that row number to get the value in column Q for that row number. Once I get the row number I know what to do to get the value in column Q for that row. My problem is getting a row number for the value "Y" when "Y" exists in a range (not a single column). Bottom line, I need the equivolent of a Match() function that works for a two-dimensional array. Is there any Excel function (or combination of functions) that does that? Thanks ... "T. Valko" wrote: It depends on what you consider the "first" location: ...........A..........B 1........X.........Y 2........Y.........X What is the first location of Y ? Is it B1 or A2 ? -- Biff Microsoft Excel MVP "WCM" wrote in message ... Then is there a best way to find location of a single literal value (or referenced value) in a two-dimensional array? Say I have alpha characters in the Excel range A1:M9. I want to find the first location of "G" in that array. Is there any function that would return say (5, 7) - i.e, for location of "G" in row-5, col-7? "T. Valko" wrote: MATCH... The lookup_array must be a one dimensional array. -- Biff Microsoft Excel MVP "WCM" wrote in message ... Excel Help indicates that the Match() function works for an Array, but I can only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Match() worksheet function
If your range is A1:M9...
Array entered** : =MIN(IF(A1:M9="G",ROW(A1:M9))) This will return the (absolute) row number of the first location of "G" from top to bottom. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "WCM" wrote in message ... That would depend on how excel does the search - whichever "Y" it runs into first. For my purpose, any cell address with the value "Y" would work. Specifically, what I need is the row number of any "Y" in the A1:M9 array - doesn't matter which "Y". I will use that row number to get the value in column Q for that row number. Once I get the row number I know what to do to get the value in column Q for that row. My problem is getting a row number for the value "Y" when "Y" exists in a range (not a single column). Bottom line, I need the equivolent of a Match() function that works for a two-dimensional array. Is there any Excel function (or combination of functions) that does that? Thanks ... "T. Valko" wrote: It depends on what you consider the "first" location: ...........A..........B 1........X.........Y 2........Y.........X What is the first location of Y ? Is it B1 or A2 ? -- Biff Microsoft Excel MVP "WCM" wrote in message ... Then is there a best way to find location of a single literal value (or referenced value) in a two-dimensional array? Say I have alpha characters in the Excel range A1:M9. I want to find the first location of "G" in that array. Is there any function that would return say (5, 7) - i.e, for location of "G" in row-5, col-7? "T. Valko" wrote: MATCH... The lookup_array must be a one dimensional array. -- Biff Microsoft Excel MVP "WCM" wrote in message ... Excel Help indicates that the Match() function works for an Array, but I can only get it to work for a 'list' (i.e., does not work for an array with more than one column - does not work for a two dimensional array). Am I missing something? Thanks in advance for you help ... Bill |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index, match, offset worksheet function | Excel Worksheet Functions | |||
| how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
| Worksheet function match - run time error | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| Match as well as does not match array function | Excel Discussion (Misc queries) | |||
Hybrid Mode
