| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match function
The Match function seems to work only on 1 dimensional arrays. Is there any
such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Hi,
Take a look at the sumproduct function http://www.xldynamic.com/source/xld.SUMPRODUCT.html "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
1000x1000 array
That's 1 million cells. Are there any duplicates? -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try
the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Not likely, but if there are finding the first one would suffice. FYI I've
had to split up the array into 250x4000 to allow it to fit within the 256 column limit "T. Valko" wrote: 1000x1000 array That's 1 million cells. Are there any duplicates? -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
With that many cells to check this will be *SLOW* plus it's volatile. You'd
probably be better off using a UDF. This will return the cell address using duplicate precedence of top to bottom, left to right. Use a helper cell to get the max value: tbl = your range =MAX(tbl) Assume that formula is entered in cell A1. For the cell address for the max value... Array entered** : =CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1 ,ROW(tbl)-MIN(ROW(tbl))+1)),0),0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... Not likely, but if there are finding the first one would suffice. FYI I've had to split up the array into 250x4000 to allow it to fit within the 256 column limit "T. Valko" wrote: 1000x1000 array That's 1 million cells. Are there any duplicates? -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Enter a text 'findme' somewhere in the range
=INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
No TV. I intended to put INDIRECT() for the below reason..
To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
(Correction) To retrieve value from the next column to the right
=INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
One of us is "out to lunch"! <g
......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Slight improvement.
Array entered: =CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)))-MIN(ROW(tbl))+1,MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1,R OW(tbl)))-MIN(ROW(tbl))+1,0),0))) That will make it a little bit more efficient. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... With that many cells to check this will be *SLOW* plus it's volatile. You'd probably be better off using a UDF. This will return the cell address using duplicate precedence of top to bottom, left to right. Use a helper cell to get the max value: tbl = your range =MAX(tbl) Assume that formula is entered in cell A1. For the cell address for the max value... Array entered** : =CELL("address",INDEX(tbl,MIN(IF(tbl=A1,ROW(tbl)-MIN(ROW(tbl))+1)),MATCH(A1,INDEX(tbl,MIN(IF(tbl=A1 ,ROW(tbl)-MIN(ROW(tbl))+1)),0),0))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... Not likely, but if there are finding the first one would suffice. FYI I've had to split up the array into 250x4000 to allow it to fit within the 256 column limit "T. Valko" wrote: 1000x1000 array That's 1 million cells. Are there any duplicates? -- Biff Microsoft Excel MVP "fireflyrt" wrote in message ... The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Not sure whether you have read the previous post...
------------ "No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.." (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment)) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: One of us is "out to lunch"! <g ......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Not sure whether you have read the previous post...
I did, but what does it have to do with what the OP is wanting to do? The OP wants the cell address of the max value in the range. There may or may not be duplicate max values. So, if there are duplicate max values then you have to decide which max value appears first: ......A.....B 1...5.....7 2...7.....3 Which max value appears first? -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Not sure whether you have read the previous post... ------------ "No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.." (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment)) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: One of us is "out to lunch"! <g ......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
You are right. Again...(as more often)...I have missed the key point
here..The below will do... =ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) ) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Not sure whether you have read the previous post... I did, but what does it have to do with what the OP is wanting to do? The OP wants the cell address of the max value in the range. There may or may not be duplicate max values. So, if there are duplicate max values then you have to decide which max value appears first: ......A.....B 1...5.....7 2...7.....3 Which max value appears first? -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Not sure whether you have read the previous post... ------------ "No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.." (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment)) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: One of us is "out to lunch"! <g ......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
The below will do...
Only if there are no duplicates! -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... You are right. Again...(as more often)...I have missed the key point here..The below will do... =ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) ) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Not sure whether you have read the previous post... I did, but what does it have to do with what the OP is wanting to do? The OP wants the cell address of the max value in the range. There may or may not be duplicate max values. So, if there are duplicate max values then you have to decide which max value appears first: ......A.....B 1...5.....7 2...7.....3 Which max value appears first? -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Not sure whether you have read the previous post... ------------ "No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.." (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment)) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: One of us is "out to lunch"! <g ......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match function
Biff is correct (as usual!) this will not work in his example above.
If the OP just wants to identify a cell containing a value, one could make use of the INDIRECT formula by giving it a name. eg define "M" to be: =INDIRECT(TEXT(MIN(IF($A$1:$H$9=MAX($A$1:$H$9),100 0*ROW($A$1:$H$9)+COLUMN($A$1:$H$9))),"r0c000"),0) Then just enter "M" in the Name Box (next to formula bar) to go to the first cell containing the Max value. You could also enter the formula directly into the EditGoto box (without the leading =). "Jacob Skaria" wrote: You are right. Again...(as more often)...I have missed the key point here..The below will do... =ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) ) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Not sure whether you have read the previous post... I did, but what does it have to do with what the OP is wanting to do? The OP wants the cell address of the max value in the range. There may or may not be duplicate max values. So, if there are duplicate max values then you have to decide which max value appears first: ......A.....B 1...5.....7 2...7.....3 Which max value appears first? -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Not sure whether you have read the previous post... ------------ "No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.." (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +<column increment)) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: One of us is "out to lunch"! <g ......A.....B.....C 1...z......y......g 2...o.....x......r =INDIRECT(ADDRESS( MIN(IF(A1:C2="x",ROW(A1:C2),"")), MIN(IF(A1:C2="x",COLUMN(A1:C2),"")))) Result = x What did that formula accomplish? If just want to know if "x" is present then use COUNTIF. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... (Correction) To retrieve value from the next column to the right =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) +1)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: No TV. I intended to put INDIRECT() for the below reason.. To retrieve a value from the same row from subsequent columns; the formula will have to be adjusted like the below...It is true that I have made an assumption that the OP is reasonably well with formulas (from the query)...and hence this way.. (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )+<column increment) If this post helps click Yes --------------- Jacob Skaria "T. Valko" wrote: Enter a text 'findme' somewhere in the range =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) That doesn't make any sense. What's the point of telling the formula to find something and then having the result of the formula being what it is you're trying to find? You probalby didn't mean to include INDIRECT. -- Biff Microsoft Excel MVP "Jacob Skaria" wrote in message ... Try the below. Enter a text 'findme' somewhere in the range A1:J20. and try the below array formula..which will retrive the value..Adjust to suit...your requirement (all in one line) =INDIRECT(ADDRESS( MIN(IF($A$1:$J$20="findme",ROW($A$1:$J$20),"")), MIN(IF($A$1:$J$20="findme",COLUMN($A$1:$J$20),"")) )) If this post helps click Yes --------------- Jacob Skaria "fireflyrt" wrote: The Match function seems to work only on 1 dimensional arrays. Is there any such function that returns the cell reference for a match in a 2d array? For example, I am trying to find the cell location of a max number in a 1000x1000 array of data - is there a function that will do that? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find the second match using the match function | Excel Worksheet Functions | |||
| how can we get unique values in match function for same match key. | 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) | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
