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)).

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)).

Hi,

Try the following array formula:

=LOOKUP(9^9,IF(C5:C410=1,B5:B410))

to make it an arry press Shift+Ctrl+Enter to enter it.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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)).

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)).

"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.

"Shane Devenshire" wrote:

Hi,

Try the following array formula:

=LOOKUP(9^9,IF(C5:C410=1,B5:B410))

to make it an arry press Shift+Ctrl+Enter to enter it.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"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, Shane, it works but it still returns the date of the FIRST occurrence of number 1.

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

"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.

"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.

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.

"T. Valko" wrote:

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

Biff,
Here's the exact formula:
=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
where ColCLastEntry = Drawings!C410 and ColBLastEntry = Drawings!B410. The
lottery results are on SHEET1('Drawings') and the 5 columns with the lottery
picks ARE formatted as numbers with NO spaces.


"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.

=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
ColCLastEntry = Drawings!C410
ColBLastEntry = Drawings!B410

Ok, I'm curious as to why you're using those named cells?

It won't work that way. Why don't you just use:

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

--
Biff
Microsoft Excel MVP


"PJ Murph" wrote in message
...


"T. Valko" wrote:

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

Biff,
Here's the exact formula:
=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
where ColCLastEntry = Drawings!C410 and ColBLastEntry = Drawings!B410. The
lottery results are on SHEET1('Drawings') and the 5 columns with the
lottery
picks ARE formatted as numbers with NO spaces.


"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.

"T. Valko" wrote:

=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
ColCLastEntry = Drawings!C410
ColBLastEntry = Drawings!B410

Ok, I'm curious as to why you're using those named cells?
Since a lottery drawing occurs on a daily basis I need to know the date and numbers of the last drawing entered. As of today there are 416 drawings on file, not 410.
It won't work that way. Why don't you just use:

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

--
Biff
Microsoft Excel MVP


"PJ Murph" wrote in message
...


"T. Valko" wrote:

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

Biff,
Here's the exact formula:
=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
where ColCLastEntry = Drawings!C410 and ColBLastEntry = Drawings!B410. The
lottery results are on SHEET1('Drawings') and the 5 columns with the
lottery
picks ARE formatted as numbers with NO spaces.


"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.

Since a lottery drawing occurs on a daily basis I
need to know the date and numbers of the last
drawing entered. As of today there are 416
drawings on file, not 410.

What does 416 have to do with it? Why were you using these defined names
that reference 410?

ColCLastEntry = Drawings!C410
ColBLastEntry = Drawings!B410


--
Biff
Microsoft Excel MVP


"PJ Murph" wrote in message
...


"T. Valko" wrote:

=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
ColCLastEntry = Drawings!C410
ColBLastEntry = Drawings!B410

Ok, I'm curious as to why you're using those named cells?
Since a lottery drawing occurs on a daily basis I need to know the date
and numbers of the last drawing entered. As of today there are 416
drawings on file, not 410.
It won't work that way. Why don't you just use:

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

--
Biff
Microsoft Excel MVP


"PJ Murph" wrote in message
...


"T. Valko" wrote:

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

Biff,
Here's the exact formula:
=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B 5:INDIRECT(ColBLastEntry))
where ColCLastEntry = Drawings!C410 and ColBLastEntry = Drawings!B410.
The
lottery results are on SHEET1('Drawings') and the 5 columns with the
lottery
picks ARE formatted as numbers with NO spaces.


"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.