LOOKUPVALUE - Return a value closest to a date in another table
Aim: Create a Calculated Column that returns the value closest to the test date.
I have a 'Growth' table (Table 1) and a 'Sprint' table (Table 2). The growth table contains the a Bio Age value. I would like to append Bio Age into the Sprint table in order to figure out what a player's Bio Age was at the time they took a sprint test. A player could have undertake a sprint test at a different time to getting their measurements taken (which are stored in the Growth table). Therefore, I need to find the Bio Age of a player that has been recorded closest to the session date that the sprint test was undertaken.
Relationship: There is no relationship between the two tables as there are duplicate values within the two - which is why I thought LOOKUPVALUE may be the best method of finding the value. PlayerID is present in both tables.
Table 1 - Growth:
Table 2 - Sprint:
Sprint Time - 5m
Any help would be greatly appreciate. Many thanks in advance.