Index / Lookup query

[Richard Buttrey]

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
__________________________

 

[Tom Ogilvy]

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

 

[Richard Buttrey]

=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
__________________________