2012 Archives by date, by thread · List index

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

```MiguelAngel wrote:
```
```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.

```
```Wow!

how to use them.

Also, I'm only using LO.  I tossed the xls up to Google docs so that
people who wanted to help could see the sheets that I was working with.

```
```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
```
```I'll have to study that formula carefully...
=MIN(IF('20120208'.B\$2:G\$1490=\$A2,ROW('20120208'.B\$2:G\$1490)," "))
Column H is the PowerPlay column - it doesn't fit into the domain for
Powerball Draws...

and the Ctrl+Shift+Enter...  I tried just Enter and got an Err: 504.  I
copied the formula for B to G, pasted, and finished with
Ctrl-Shift-Enter, and it worked.

Thank you for your help, Miguel.
use the Ctrl-Shift-Enter.

Back to the design table. :)

Paz,

--
Barry Smith
e bnsmith001@gmail.com
w http://bit.ly/l8QJup

--
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
```