Mike's formula should work. If it doesn't then there may be a problem with
your data. The last instance of 1 may be a TEXT number and not a numeric
number. In Excel, these *usually* don't match. There may be unseen
characters like spaces that make it a TEXT entry:

<space1
1<space
<space1<space

Post the *exact* formula you tried that still doesn't work.

--
Biff
Microsoft Excel MVP


"PJ Murph" wrote in message
...


"Mike H" wrote:

Hi,

I'd would very much like to see the set of data that made the lookup
version
return the first occurrence when there is a second occurrence. If it does
for
you then the second occurrence may not be what you think it is. Is it
really
a number 1 or is it text, or a number rounded to look like a 1.


Mike

Hi,
These are lottery drawings. Column A is the number of the drawing
starting at 1, with 410 lotteries on file. Column B is the date of the
drawing, starting with 02/01/2008 thru 03/20/2009. Columns C thru G are
the
CASH 5 lottery picks. The number 1 appears 59 times in column C (Countif).
I
want the date when the number 1 was last chosen.

"PJ Murph" wrote:



"Mike H" wrote:

Hi,

I just tested you index match formula a bit more thoroughly and there
are
lots of ways to make it fall over, use the lookup instead

Mike

"Mike H" wrote:

Hi,

Your formula wouldn't find anything becasue there was a syntax
error, try this

=INDEX(B5:B410,MATCH(1,C5:C410),0)

or another

=LOOKUP(2,1/(C5:C410=1),B5:B410)


Mike

"PJ Murph" wrote:

I'm trying to find the date (which is in column B) of the LAST
occurrence of
a number (in this instance number 1) in column C. This formula
only locates
the first occurrence; =INDEX(B5:B410),MATCH(1,C5:C410),0)).

Thanks, Mike H, it works but it still returns the
date of the FIRST occurrence of number 1.