# is in column B
Amount is in column D
Date is in column J
=INDEX(J$2:J$10000,MATCH(1,(B2=B$2:B$10000)*(D2=D$2:D$10000),0))
Using above index match formula to show date in column J only if there is a match with data in column B & D.
Trouble is there could be more than one occurence so i only want it to show the most recent date.
Is there any way to get the match/index formula to always show the most recent date if there is more than one occurence where data in column B & D match up?
Last edited by scabertrain; 09-27-2010 at 05:11 PM.
Try this:
=LOOKUP(2, 1/($B$2:$B$10000=$B2)*($D$2:$D$10000=$D2), $J$2:$J$10000)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
that did the trick. However i failed to mention that if there is no data in J column for that specific row match of B & D then it needs to ignore it. with the above if the cell is blank it's displaying 1/1/1900, i need it to display the most recent actual date.
Ouch...well, this is the corrected formula...
=LOOKUP(2, 1/($B$2:$B$28&"-"&$D$2:$D$28=$B2&"-"&$D2)*($J$2:$J$28/$J$2:$J$28), $J$2:$J$28)
The bad news is if you change those ranges back to 10000 cells like in your original example it will put upwards of a million calculations in every cell, instant doorstop. Only use the range actually required.
exactly what i needed. thx!!
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks