Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index, match, multiple IFs query | Excel Worksheet Functions | |||
if, Isnumber, Match, &index query | Excel Discussion (Misc queries) | |||
index and match query | Excel Worksheet Functions | |||
INDEX AND MATCH QUERY ? | Excel Worksheet Functions | |||
Help please! Lookup/Index query. | Excel Discussion (Misc queries) |