Greetings
=MIN(IF(B3:B32="",IF(A3:A32>0,A3:A32))) ArrayedOldest Date Array.xlsx
Following on from a previous post need to add to my (forums) array.
I need to find the location of a date which has been looked up in a cell based on the above formula which is in the attached.
Help is always appreciated.
Thank you
Matt
=ADDRESS(MATCH(C4,$A$3:$A$32,0)+ROWS($A$1:$A$2),1)
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
It doesn't seem to like looking at the array's earliest date that it brings back.
I wonder if it is because there are more than 1 potential cell locations with the same date. Which in that case would mean that I would only need the first location it can find, lowest row location.
The formula provided by ChemistB returns cell address $A$7 which is corresponds to the date in C4. If you want to incorporate earliest date into his formula you can do it too
=ADDRESS(MATCH(MIN(IF(A3:A32>0,A3:A32)),$A$3:$A$32,0)+ROWS($A$1:$A$2),1)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks