Array Formula Help

K

kcmigs

Spreadsheet consists of

ColA Dates, increasing by one.... 1/1/2003 onwards
ColB random X's indicating attendance

Need an array formula when inputted with
a number (5,15,25, etc) returns the date of
the occurance (5th, 15th, 25th, etc)

i.e. input =5, then 5th occurance (5th X) on 4/19/200
 
B

Bob Phillips

=INDEX(10:10,1,5)

checks row 10

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
S

Stephen Bye

Something tells me that this should be possible with an array formula, but
here's a 'simpler' solution not using one:
1) In a spare column (in this example I have used column E), count the
number of 'X's entered so far by entereing this formula into each row:
=COUNTIF(B$1:INDIRECT("B"&ROW()),"X")
2) Then to get the date corresponding to the Nth 'X', use a lookup function
to find the first occurrence of the number you are interested in, and then
get the corresponding date from column A, by entering this formula in the
cell where you want your result:
=OFFSET($A$1,MATCH(number,E:E,0)-1,0)
where 'number' is the number of the 'X' you are looking for (e.g. 5, 15, 25
etc.) and E:E is the column where we stored the cumulative counts from part
(1).
 
Top