I have a table that has the dates of the year A1:A365. Then I have operator names (Joe, Mary, Susan) in the B column. Each day, a name gets added to the list. How do I search for the latest/last entry and return the date? =(MAX(A1:A365)) gives me 12/31/2021, but I want the date of the last name entered in the B column that will update each time a name is added.
Any help is appreciated.
6/28/2021 Joe
6/29/2021 Mary
7/1/2021 Mary
7/2/2021 Susan
7/3/2021
7/4/2021
7/5/2021
7/6/2021
Last edited by localheromx; 07-12-2021 at 01:18 PM.
Try this in Column C to get the date, it should return the date of the last entry
Sorry i had deleted a column after i posted and didnt update the formula
Please Login or Register to view this content.
In column C place the above corrected formula.
Last edited by cubangt; 07-12-2021 at 02:17 PM.
If you find the suggestion or solution helpful, please consider adding reputation to the post.
Hmmm... it didn't work for me:
TRy
=LOOKUP(2,1/(B1:B7<>""),A1:A7)
That did it! ThanksOriginally Posted by JohnTopley
corrected formula in previous post.
One more twist..... What if I wanted the 2nd to last entry? (7-1-2021)
I don't know if this is the correct way or not, but adding -1 to the end worked. =LOOKUP(2,1/(B1:B7<>""),A1:A7)-1
If you are using my suggestion you can subtract 1 row like so;
Notice the -1 in boldPlease Login or Register to view this content.
=INDEX(A:B,MATCH(LOOKUP(2,1/(B:B<>""),B:B),B:B,0)-1,1)
Try
=INDEX(A1:A7,MATCH(LOOKUP(2,1/(B1:B7<>""),A1:A7),A1:A7,0)-1)
These suggestions all work! But I keep running into other issues..... What if there wasn't an entry in the middle? Each of these solutions find the most recent (Date & Name) and then -1 row. Instead of -1 Date & Name combination. How do I return 7-6-2021 Toukta (in my example) as the next to last Date & Name?
Screenshot 2021-07-13 043657.png
Try
=INDEX($A$1:$A$7,AGGREGATE(15,6,ROW($A$1:$A$7)/($B$1:$B$7<>""),COUNTIF($B$1:$B$7,"<>")-1))
Genius! Thanks a lot for your help.
Or try:
=INDEX($A$1:$A$7,AGGREGATE(14,6,ROW($A$1:$A$7)/($B$1:$B$7<>""),2))
Array formula
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks