Reformatting data

Apologies if this has been asked and answered, I could not find it.

I have a bunch of data in the following format. For a group of people I have observations taken at different times. For example John's weight last November and then again yesterday. Over time the people measured come and go but I keep track of all the observations. The observations have no predetermined dates, so John could have 5 measurements and Sally only 2 (and all in different dates). To keep it simple let's say I have all this data in Sheet 1, where Column A is the name of the person, Column B the date of the measurement, and Column C the measurement itself. I want to make the following calculations in a new Sheet 2:

1) I want to be able to calculate, for any given time, the value of any measurement for any person. So, for example, I want to find Sally's weight at the beginning of every month of the year. On column A of Sheet 2 I'd have Sally's name and Column B the month (in Row 1 Jan, row 2 February and so on). My question refers how to obtain column C. The function in Column C would need to check Column A in Sheet 2 and then find all the rows in Sheet 1 that have the same name in Column A (all the Sallies, for example). It would then check the date of each measurement in Column B of Sheet 1 until it found one whose date was equal, or prior to, the date in Column B of Sheet 2. The result for Column C in Sheet 2 would then be the Column C from Sheet 1 of the identified row.

Of course, it would not be enough to simply find a measurement prior to the date in Sheet 2. I need to make sure it's the latest possible, so it would need to check that the measurement was the closest to the target month. My data has, in Sheet 1, a Column D with the date of the next measurement (when available). So one way would be to check that the month was somewhere between the two dates.


2) The second thing I want to do is be able to calculate, for any given date, how many people are being measured. Not all started at the same time, some may have begun years after the first ones. So here the idea would be to compare the target month with the first measurement date of any person.

Hopefully this is somewhat clear. Any suggestions how to do this?

Thanks in advance.

PS: added a file as an example