How do I make the weight column in my medical log sheet get updated with the most recent weigh-in?

Question

I am looking to get the weight column in my medical log sheet to be updated automatically when I key in the new weights on my Weight Log sheet. I take the weights weekly and update the info into a new column. How could I do this? Please note I want to keep the log so I dont want to have to delete past weigh ins.

Answer 1

Use reduce() and filter(), like this:

=reduce( 
  "", filter('Weight Log'!B$2:Z, 'Weight Log'!A$2:A = A2), 
  lambda( 
    result, current, 
    if( len(current), current, result ) 
  ) 
)