Identifying the row and column of a table value

[JHB]

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

 

[T. Valko]

...........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.

 

[JHB]

..........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.

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))),"")

 

[T. Valko]

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

..........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.

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))),"")

 

[Bernie Deitrick]

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

 

[Bernie Deitrick]

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]

That's possible. I read it as return the column header that corresponds to
the leftmost instance (any row) of the lookup value.