Index / Lookup query
 

This is a slight variation of my question yesterday

I have a table G7:AK23 (31 columns, one for each day of the month)
containing a mixture of nulls, zeros and numbers (positive and
negative)

G5:AK5 contain date numbers

In A7:A23 e.g. say A7, I want a formula which finds the first value,
(pos or neg) in G7:AK7 which is not zero or null, and returns the date
from row 5 of the column which contains this first value.

I'm sure some combination of Match and Index is probably a solution,
but Im struggling to find a solution.

Usual TIA.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Index / Lookup query
 

=INDEX($A$5:$AK$5,1,SMALL(IF((ISNUMBER($G$7:$AK$7) *($G$7:$AK$7<0)),COLUMN($
G$7:$AK$7)),1))

Entered in A7 with Ctrl+Shift+Enter since it is an array formula.

--
Regards,
Tom Ogilvy


"Richard Buttrey" wrote in
message ...
This is a slight variation of my question yesterday

I have a table G7:AK23 (31 columns, one for each day of the month)
containing a mixture of nulls, zeros and numbers (positive and
negative)

G5:AK5 contain date numbers

In A7:A23 e.g. say A7, I want a formula which finds the first value,
(pos or neg) in G7:AK7 which is not zero or null, and returns the date
from row 5 of the column which contains this first value.

I'm sure some combination of Match and Index is probably a solution,
but Im struggling to find a solution.

Usual TIA.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Index / Lookup query
 

On Wed, 19 Jan 2005 08:47:58 -0500, "Tom Ogilvy"
wrote:

=INDEX($A$5:$AK$5,1,SMALL(IF((ISNUMBER($G$7:$AK$7 )*($G$7:$AK$7<0)),COLUMN($
G$7:$AK$7)),1))

Thanks again Tom.

Perfect answer as usual.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________