|
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 |
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 |
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 |
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 |
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 |
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 |
Match() worksheet function
That is perfect! Thank you ...
"T. Valko" wrote: 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 |
Match() worksheet function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "WCM" wrote in message ... That is perfect! Thank you ... "T. Valko" wrote: 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 |
Match() worksheet function
for posterity, here is final formula, and it's working as advertised ...
thanks again: {=INDIRECT("S"&TEXT(MIN(IF($D$1:$P$5000=A1,ROW($D$ 1:$P$5000))),"0"))} "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "WCM" wrote in message ... That is perfect! Thank you ... "T. Valko" wrote: 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 |
Match() worksheet function
You'll get the same result without the TEXT function:
{=INDIRECT("S"&MIN(IF($D$1:$P$5000=A1,ROW($D$1:$P$ 5000))))} -- Biff Microsoft Excel MVP "WCM" wrote in message ... for posterity, here is final formula, and it's working as advertised ... thanks again: {=INDIRECT("S"&TEXT(MIN(IF($D$1:$P$5000=A1,ROW($D$ 1:$P$5000))),"0"))} "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "WCM" wrote in message ... That is perfect! Thank you ... "T. Valko" wrote: 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 |
Match() worksheet function
Got it ... thanks again ... this exercise totally got me into array formulas - a whole new world ... "T. Valko" wrote: You'll get the same result without the TEXT function: {=INDIRECT("S"&MIN(IF($D$1:$P$5000=A1,ROW($D$1:$P$ 5000))))} -- Biff Microsoft Excel MVP "WCM" wrote in message ... for posterity, here is final formula, and it's working as advertised ... thanks again: {=INDIRECT("S"&TEXT(MIN(IF($D$1:$P$5000=A1,ROW($D$ 1:$P$5000))),"0"))} "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "WCM" wrote in message ... That is perfect! Thank you ... "T. Valko" wrote: 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 |
| All times are GMT +1. The time now is 07:52 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com