 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008Match() 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... The lookup_array must be a one dimensional array.  SignatureBiff Microsoft Excel MVP > Excel Help indicates that the Match() function works for an Array, but I > can [quoted text clipped - 5 lines] > > Bill 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? > MATCH... > [quoted text clipped - 9 lines] > > > > Bill 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 ? SignatureBiff Microsoft Excel MVP > Then is there a best way to find location of a single literal value (or > referenced value) in a two-dimensional array? [quoted text clipped - 18 lines] >> > >> > Bill 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 ... > It depends on what you consider the "first" location: > [quoted text clipped - 28 lines] > >> > > >> > Bill 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) SignatureBiff Microsoft Excel MVP > That would depend on how excel does the search - whichever "Y" it runs > into [quoted text clipped - 51 lines] >> >> > >> >> > Bill That is perfect! Thank you ... > If your range is A1:M9... > [quoted text clipped - 63 lines] > >> >> > > >> >> > Bill You're welcome. Thanks for the feedback! SignatureBiff Microsoft Excel MVP > That is perfect! Thank you ... > [quoted text clipped - 76 lines] >> >> >> > >> >> >> > Bill 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"))} > You're welcome. Thanks for the feedback! > [quoted text clipped - 78 lines] > >> >> >> > > >> >> >> > Bill You'll get the same result without the TEXT function: {=INDIRECT("S"&MIN(IF($D$1:$P$5000=A1,ROW($D$1:$P$5000))))} SignatureBiff Microsoft Excel MVP > for posterity, here is final formula, and it's working as advertised ... > thanks again: [quoted text clipped - 90 lines] >> >> >> >> > >> >> >> >> > Bill Got it ... thanks again ... this exercise totally got me into array formulas - a whole new world ... > You'll get the same result without the TEXT function: > [quoted text clipped - 94 lines] > >> >> >> >> > > >> >> >> >> > Bill |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.