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

[Jeff]

My formula in A48 is: =INDEX(C1:C7,MATCH(A49,D17,0))
My formula in A49 is: =MIN(D17)
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

 

[JP]

One way:

=INDEX(C1:C7,RANK(SMALL(D17,2),D17,0)+(COUNTA(SMALL(D17,2)-
(SMALL(D17,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

 

[Jeff]

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

 

[Max]

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(D17=MIN(D17),C1:C7)),IF(D17=MIN(D17),C1:C7),0))

---

 

[Jeff]

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]

Welcome, Jeff. Glad it worked, and thanks for feeding back