Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

My formula in A48 is: =INDEX(C1:C7,MATCH(A49,D1:D7,0))
My formula in A49 is: =MIN(D1:D7)
Column "C" is a list of dates (stored as numbers) in consecutive order
Column "D" is random numbers that can be the same as another number in the
same column.

C D
1 39545 183
2 39546 222
3 39547 217
4 39548 105
5 39549 195
6 39550 175
7 39551 105

Note that D4 and D7 happen to be the same number... My forumal in A48 is
displaying the date from C4 but I need it to display the newest date in C
that corresponds with the lowest number in D... In this case it would be C7.

Thank you

Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

One way:

=INDEX(C1:C7,RANK(SMALL(D1:D7,2),D1:D7,0)+(COUNTA( SMALL(D1:D7,2)-
(SMALL(D1:D7,2)-1))),1)

I solved your specific problem, but it might not work with other data.

(Modified from http://www.cpearson.com/excel/rank.aspx)

HTH,
JP

On Apr 14, 2:46*pm, Jeff wrote:
My formula in A48 is: =INDEX(C1:C7,MATCH(A49,D1:D7,0))
My formula in A49 is: =MIN(D1:D7)
Column "C" is a list of dates (stored as numbers) in consecutive order
Column "D" is random numbers that can be the same as another number in the
same column.

* * * * C * * * *D
1 * 39545 * 183
2 * 39546 * 222
3 * 39547 * 217
4 * 39548 * 105
5 * 39549 * 195
6 * 39550 * 175
7 * 39551 * 105

Note that D4 and D7 happen to be the same number... My forumal in A48 is
displaying the date from C4 but I need it to display the newest date in C
that corresponds with the lowest number in D... In this case it would be C7.

Thank you

Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

Thanks "JP" for trying but you are correct that it only works for this
particular example...If there are more than two duplicates it won't work.
After I tried your formula a light bulb went off in my head... All I have to
do is sort column C descending and in column D work from the bottom up and it
works... But for some reason this bothers me that I can't solve this with a
formula.

I still would like to know if there is a formula for this.

Thanks, Jeff

"JP" wrote:

One way:

=INDEX(C1:C7,RANK(SMALL(D1:D7,2),D1:D7,0)+(COUNTA( SMALL(D1:D7,2)-
(SMALL(D1:D7,2)-1))),1)

I solved your specific problem, but it might not work with other data.

(Modified from http://www.cpearson.com/excel/rank.aspx)

HTH,
JP

On Apr 14, 2:46 pm, Jeff wrote:
My formula in A48 is: =INDEX(C1:C7,MATCH(A49,D1:D7,0))
My formula in A49 is: =MIN(D1:D7)
Column "C" is a list of dates (stored as numbers) in consecutive order
Column "D" is random numbers that can be the same as another number in the
same column.

C D
1 39545 183
2 39546 222
3 39547 217
4 39548 105
5 39549 195
6 39550 175
7 39551 105

Note that D4 and D7 happen to be the same number... My forumal in A48 is
displaying the date from C4 but I need it to display the newest date in C
that corresponds with the lowest number in D... In this case it would be C7.

Thank you

Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

I still would like to know if there is a formula for this.

Think this returns what you're after, array-entered (press CTRL+SHIFT+ENTER
to confirm the formula):
=INDEX(C1:C7,MATCH(MAX(IF(D1:D7=MIN(D1:D7),C1:C7)) ,IF(D1:D7=MIN(D1:D7),C1:C7),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

Thanks Max,

I love it! It worked great!

I did finally manage to find other ways to get what I was after, but I'm
going to use yours because it looks (or is) so much more professional (you
should see the stuff I was doing).

Thanks again,
Jeff

"Max" wrote:

I still would like to know if there is a formula for this.

Think this returns what you're after, array-entered (press CTRL+SHIFT+ENTER
to confirm the formula):
=INDEX(C1:C7,MATCH(MAX(IF(D1:D7=MIN(D1:D7),C1:C7)) ,IF(D1:D7=MIN(D1:D7),C1:C7),0))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Problem With Duplicate Enteries Using =INDEX(#,MATCH(#,#,#))
 

Welcome, Jeff. Glad it worked, and thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jeff" wrote in message
...
Thanks Max,

I love it! It worked great!

I did finally manage to find other ways to get what I was after, but I'm
going to use yours because it looks (or is) so much more professional (you
should see the stuff I was doing).

Thanks again,
Jeff