|
Identifying the row and column of a table value
I am, not certain which function to use for the following problem:
I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker |
Identifying the row and column of a table value
...........A..........B..........C
1.......H1........H2........H3 2.......10.........12.........12 3.......12.........14.........13 4.......16.........15.........11 So, if your lookup value is 12 you want the header that corresponds to 12 found in cell A3? If that's what you want try this array formula** : =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))),"") ** 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 "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker |
Identifying the row and column of a table value
On Jan 28, 1:44*pm, "T. Valko" wrote:
..........A..........B..........C 1.......H1........H2........H3 2.......10.........12.........12 3.......12.........14.........13 4.......16.........15.........11 So, if your lookup value is 12 you want the header that corresponds to 12 found in cell A3? If that's what you want try this array formula** : =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))),"") ** 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 "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker Thanks for your response. I am slightly confused by the formula, and specifically the "COUNTIF" AT THE START..IS THIS SUPPOSED TO BE JUST COUNT OR IS THERE SOME OTHER ELEMENT THAT GOT SCRAMBLED? tHANKS FOR YOUR HELP John Baker =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN (COLUMN(A2:C4))+1))),"") |
Identifying the row and column of a table value
The COUNTIF is a form of an error trap.
It's making sure the lookup value does in fact exist otherwise the formula would return the leftmost header which would be an incorrect result. If the lookup value does not exist the formula returns a blank instead. If the lookup value will *always* exist you can remove the COUNTIF from the formula. Still array entered** : =INDEX(A1:C1,MIN(IF(A2:C4=12,COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))) ** 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 "JHB" wrote in message ... On Jan 28, 1:44 pm, "T. Valko" wrote: ..........A..........B..........C 1.......H1........H2........H3 2.......10.........12.........12 3.......12.........14.........13 4.......16.........15.........11 So, if your lookup value is 12 you want the header that corresponds to 12 found in cell A3? If that's what you want try this array formula** : =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))),"") ** 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 "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker Thanks for your response. I am slightly confused by the formula, and specifically the "COUNTIF" AT THE START..IS THIS SUPPOSED TO BE JUST COUNT OR IS THERE SOME OTHER ELEMENT THAT GOT SCRAMBLED? tHANKS FOR YOUR HELP John Baker =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN (COLUMN(A2:C4))+1))),"") |
Identifying the row and column of a table value
John,
Array enter this (enter using Ctrl-Shift-Enter) assuming your table starts in cell A1, and the value of interest is in cell AU1 =INDEX(1:1,(MATCH(AU1,OFFSET(A1,0,MIN(IF(--(A1:AS26=AU1)<0,COLUMN(A1:AS26)))-1,ROWS(A1:AS26)),FALSE))) & " " & INDEX(A:A,MIN(IF(--(A1:AS26=AU1)<0,COLUMN(A1:AS26)))) HTH, Bernie MS Excel MVP "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker |
Identifying the row and column of a table value
Biff,
The OP asked for both headers, I think... want to find the vertical leftmost cell value and the heading cell value for a given number in the grid Bernie MS Excel MVP "T. Valko" wrote in message ... The COUNTIF is a form of an error trap. It's making sure the lookup value does in fact exist otherwise the formula would return the leftmost header which would be an incorrect result. If the lookup value does not exist the formula returns a blank instead. If the lookup value will *always* exist you can remove the COUNTIF from the formula. Still array entered** : =INDEX(A1:C1,MIN(IF(A2:C4=12,COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))) ** 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 "JHB" wrote in message ... On Jan 28, 1:44 pm, "T. Valko" wrote: ..........A..........B..........C 1.......H1........H2........H3 2.......10.........12.........12 3.......12.........14.........13 4.......16.........15.........11 So, if your lookup value is 12 you want the header that corresponds to 12 found in cell A3? If that's what you want try this array formula** : =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))),"") ** 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 "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker Thanks for your response. I am slightly confused by the formula, and specifically the "COUNTIF" AT THE START..IS THIS SUPPOSED TO BE JUST COUNT OR IS THERE SOME OTHER ELEMENT THAT GOT SCRAMBLED? tHANKS FOR YOUR HELP John Baker =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN (COLUMN(A2:C4))+1))),"") |
Identifying the row and column of a table value
That's possible. I read it as return the column header that corresponds to
the leftmost instance (any row) of the lookup value. -- Biff Microsoft Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Biff, The OP asked for both headers, I think... want to find the vertical leftmost cell value and the heading cell value for a given number in the grid Bernie MS Excel MVP "T. Valko" wrote in message ... The COUNTIF is a form of an error trap. It's making sure the lookup value does in fact exist otherwise the formula would return the leftmost header which would be an incorrect result. If the lookup value does not exist the formula returns a blank instead. If the lookup value will *always* exist you can remove the COUNTIF from the formula. Still array entered** : =INDEX(A1:C1,MIN(IF(A2:C4=12,COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))) ** 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 "JHB" wrote in message ... On Jan 28, 1:44 pm, "T. Valko" wrote: ..........A..........B..........C 1.......H1........H2........H3 2.......10.........12.........12 3.......12.........14.........13 4.......16.........15.........11 So, if your lookup value is 12 you want the header that corresponds to 12 found in cell A3? If that's what you want try this array formula** : =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN(COLUMN(A2:C4))+1))),"") ** 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 "JHB" wrote in message ... I am, not certain which function to use for the following problem: I have an array 45 columns long by 26 rows. This array contains numerical references, and I want to find the vertical leftmost cell value and the heading cell value for a given number in the grid (array). There will be only one iteration of a value in a given column, but will be multiple iteration in a given row. Excel 2000 Any help would be much appreciated. John Baker Thanks for your response. I am slightly confused by the formula, and specifically the "COUNTIF" AT THE START..IS THIS SUPPOSED TO BE JUST COUNT OR IS THERE SOME OTHER ELEMENT THAT GOT SCRAMBLED? tHANKS FOR YOUR HELP John Baker =IF(COUNTIF(A2:C4,12),INDEX(A1:C1,MIN(IF(A2:C4=12, COLUMN(A2:C4)-MIN (COLUMN(A2:C4))+1))),"") |
| All times are GMT +1. The time now is 01:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com