Re: [libreoffice-users] Spreadsheet -- General searching guidance...

El 12/02/12 16:04, Barry Smith escribió:
> Thank you for your help, Miguel.
>
> > =MATCH(N(A2),'20120208'!B$2:B$1491,0)
>
> That is part of the problem.  That says within the WB1 column, that
> there is a match on the 122 row, but the actual row is 123... so MATCH
> gives a row relative to the top of the vector.  That is even better than
> I was hoping for (I was thinking that I'd have to subtract a scalar
> because the matrix started in Row 2 (or 3, since I'm trying to create
> the stats before the draw on 2/8).
> So, I dupe the MATCH function for each WB column, and can I then use MIN
> to find the first occurrence...
>
> Something like
> =MIN( MATCH(N(A2),'20120208'!B$2:B$1491,0),
> MATCH(N(A2),'20120208'!C$2:C$1491,0),
> MATCH(N(A2),'20120208'!D$2:D$1491,0),
> MATCH(N(A2),'20120208'!E$2:E$1491,0),
> MATCH(N(A2),'20120208'!F$2:F$1491,0) )
>
> That would give me the lowest row in the matrix where N(A2) is found...
> I think.
>
> Thank you for your help, Miguel.
>
> Paz, (escribo español también)
For you can use a matrix fomula:
=ArrayFormula(MIN(IF('20120208'!B$2:H$1490=$A2;ROW('20120208'!B$2:H$1490);999999)))
This is how is view in google docs,

To enter in LibreOffice, intruduce the formula:
=MIN(IF('20120208'.B$2:H$1490=$A2;ROW('20120208'.B$2:H$1490);999999))
and finish with Crtl+Shift+Enter

Take care of sheet separator, if you use Libo or google docs.

Miguel Ángel.


--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted