Categories: Ask a "how-to" question : Sheets :

First value in a filtered array

Showing 1-4 of 4 messages
First value in a filtered array Uli1 6/2/11 2:25 AM
Firefox/Mac OSX):
ulrich.dinkelbach(@gmail,

Hi together,

I'd like to have the first, last, (n) value of a spreadsheet filtered by this:

=(FILTER(B$11:B139;Month(A$11:A139)=5;INDEX(A$11:A139;1)))

But the the part (INDEX...) seems, that it doesn't work.

Is there any other possibilty to get first, last or any other value?

Thanks a lot in advance!

Uli



Re: First value in a filtered array AD:AM 6/2/11 5:07 AM
Hi

To get the first:

=INDEX( FILTER( B$11:B139 ; Month( A$11:A139 ) = 5 ) ; 1 )

To get the last:

=FILTER( B$11:B139 ; ROW( A$11:A139 ) = MAX( FILTER( ROW( A$11:A139 ) ; Month( A$11:A139 ) = 5 ) ) )

To get the nth:

=FILTER( B$11:B139 ; ROW( A$11:A139 ) = SMALL( FILTER( ROW( A$11:A139 ) ; Month( A$11:A139 ) = 5 ) ; n ) )

HTH
Adam

Re: First value in a filtered array AD:AM 6/2/11 5:18 AM
edit.. apologies, returning the nth item in the filter needn't be so complicated:

=INDEX( FILTER( B$11:B139 ; Month( A$11:A139 ) = 5 ) ; n )
Re: First value in a filtered array Uli1 6/2/11 7:04 AM
Hi Adam,

Thank you so much -works perfectly!

Uli