1. ## Unusual Table Lookup

The attached Vital Statistics table is part of a worksheet in a many-paged workbook. It keeps track of lottery drawings during a three-month period. Here are some characteristics of the drawings it keeps track of:

• The drawings are always on Tue, Wed, Fri, and Sat.
• Depending on the calendar, the first drawing in a period can be on any one of these days.
• Also depending on the calendar, there can be different numbers of PowerBall (PB) and Mega Millions (MM) drawings during the three-month period.

The workbook is set up so that the cells in the attached table are automatically filled in each time a new period starts.

As soon as the day of a drawing ends, conditional formatting grays out that day's entry in the table. I need one thing more:

When the day of a drawing ends, I want Excel to plug the value of "Minimum Winnings to Go" from the NEXT drawing into cell M10.

I've fooled around with this for a while, but my knowledge doesn't seem up to the task. Who can help?

2. Hay Lou,

Enter the formula in M10:

Code:
`=INDEX(B4:J55,MATCH(MAX(IF(D4:D55<TODAY(),D4:D55,"")),D4:D55,0),6)`
This is an array formula so you will have to use Ctrl-Shift_Enter after pasting into cell (Note the curly brackets in the image). It will show the latest Winnings to go prior to today at 00:00.

HTH,
Maud

LouS1.png

3. Lou,

I may have misunderstood you. If you want the NEXT drawing then use this array formula instead in M10:
Code:
`=INDEX(B4:J55,MATCH(MIN(IF(D4:D55>=TODAY(),D4:D55,"")),D4:D55,0),6)`
Maud

PS. Don't forget to use Ctrl-Shift-Enter

4. ## The Following User Says Thank You to Maudibe For This Useful Post:

Lou Sander (2015-05-22)

5. If the data is sorted in date order as it appears to be in the sample, then you can use a regular formula:
=INDEX(G4:G55,MATCH(TODAY(),D4:D55,1)+1)

6. Rory,

Yes, forgot about the match_type of 1 in the match formula.

7. Every time I think I know something about Excel, the depth of my ignorance is revealed by something like this!

The dates ARE always sorted from low to high.

Maudibe's Comment #3 seems to work right now. (On the morning of Fri 05/22/15, the Minimum Winnings To Go is \$80.00, which is the value appearing in M10 if I use that formula.) There is a drawing tonight, so we'll see what happens at midnight!

rory's formula currently gives \$77.00, which is the next Minimum Winnings To Go after the one I want. (On Friday morning, it gives Saturday's entry. Though it's Friday, the drawing isn't until the end of the day, so Friday's MWTG is good through the end of the day.)

8. If you want to match today, remove the +1 at the end of mine.

9. ## The Following User Says Thank You to rory For This Useful Post:

Lou Sander (2015-05-22)

10. That makes it succeed for today. We'll see what happens at midnight, when today's drawing is no longer the next one.

11. After action report: Both versions successfully handled the transition from Friday to Saturday. We'll see what happens over the next few days.

12. Thanks for the follow-up Lou

13. More followup: Maudibe's formula successfully transitioned from Saturday to Sunday, putting Tuesday's \$73.00 MinWinningsToGo into the box. The other one failed, leaving Saturday's \$77.00 there.

I'll keep observing this through a full week. I'll also start figuring out how the INDEX function works. (Also array formulas and all the other stuff.)

14. Hi Lou

..I think Maud should get a share of the winnings then.

zeddy

15. GMTA! Pool participants contribute \$25 each quarter, in return for a share in any jackpot we win. We spend all the contributions on tickets. Many loungers have contributed to our very-highly-developed Excel workbook, which keeps track of participants, contributions, numbers played, bank balances, winnings, etc.

We select our tickets so we are assured of winning something in every drawing. Typically we spend \$3,000 on tickets each quarter, and win \$250 or so. (It goes without saying that the \$250 is unimportant -- we are looking to win a multimillion dollar jackpot.)

The \$250 goes toward expenses and a "kitty" to buy extra tickets when the jackpot is over \$100 million.

You can see the whole story on our website at www.ussrankin.org/lottery

I promise the person who sells us the tickets that we will give them the car of their choice if they sell us a jackpot winner. (No million dollar sports cars -- a \$130,000 Mercedes would be the max.)

Depending on Maud's preference, I could offer him a share in the next pool (big potential payoff, but very little chance of seeing it), or maybe buy him a little something out of the kitty (minimal dollar value, but 100% chance of getting it).

16. Hi Lou

..just give him an extra thank you click then.

We will miss your posts here if you get that \$100million ticket.

zeddy

17. I'll reinvest my share into Lou's purchasing of his own Blog site.

Lous secrets.png

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•