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

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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

---
 
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
 
Back
Top