How do I index the LAST occurrence of a duplicate number in a
"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.
|