How do I index the LAST occurrence of a duplicate number in a colu

[PJ Murph]

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]

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

 

[Shane Devenshire]

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.

 

[Mike H]

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

 

[PJ Murph]

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

 

[PJ Murph]

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

 

[Mike H]

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]

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.

 

[T. Valko]

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:

<space>1
1<space>
<space>1<space>

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

 

[PJ Murph]

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:

<space>1
1<space>
<space>1<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!B5: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.

 

[T. Valko]

=LOOKUP(2,1/(Drawings!C5:INDIRECT(ColCLastEntry)=1),Drawings!B5: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)

 

[PJ Murph]

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)

 

[T. Valko]

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