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