[libreoffice-users] Calc: Searching for values in a certain range string

Hi,

I am looking for a pointer or two to enable me to accomplish what I seek.
What I seek is to be able to have a sheet of Lottery results downloaded 
from the Internet and pasted into a Calc sheet. On another Calc sheet I 
will have a listing of the 6 numbers held for each specific draw date.

Each populated cell  of my ticket sheet will indicate if the number in 
that cell is the same as a number in a range of cells from the results 
sheet, on a particular date.

As an example:

Assume the draw results below dated  drew the following numbers:
04/02/2014 16, 03, 19, 21, 23, 06       
11/02/2014 17, 05, 02, 20, 22, 04
18/02/2014 09, 40, 05, 09, 42, 03

Also assume that draw numbers I chose for a draw on a date were:
11/02/2014  02, 05, 18, 20, 22, 04

In theory looking at the data, if I submitted my ticket for prize 
redemption, I might score very well considering my ticket matches 5 out 
of 6 of the numbers from the draw. If any number on my ticket matches a 
corresponding date value, I want the cell to change background colour.

Now the trick is to put this in practice. :)

After looking into the usage of vlookup and given I want the background 
colour to change if the conditions are met, I selected the cell 
containing the first number on my ticket (02) and then selected 
'Format', Conditional Formatting'.

I selected that the cell value is equal to and entered 
vlookup($LottoResults.$B$39,$LottoResults.$C$39:$D$39,1,0) into the 
field, chose the new background style and said OK.

I checked the first number of my ticket on the specified date and 
noticed that the background had not changed as instructed to by the 
conditional formatting. :(

Using the other option on Conditional Formatting I selected 'Formula is' 
and entered the same vlookup formula wit the same disastrous results, 
i.e. no colour change on the first cell of my ticket number :(

Doing a second trial I said cell value must equal and entered 
$LottoResults.$C$39.
The cell on my ticket came out the requested colour however the formula 
did not allow me to check if on the ticket date and corresponding ticket 
draw were identical. :(

The purchased ticket has 6 numbers on it, with the first 5 being in 
numerical order and the 6th being a bonus random ball( 01-49).
The draw results are not in numerical order.

So close yet so far, what am I missing?

pointers?

Regards
Hylton

--
To unsubscribe e-mail to: users+unsubscribe@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