Clash Royale CLAN TAG#URR8PPP

Match Rows By ID and return a value from the row with the closet date before a specified date

I'm using Google Sheets and have dataset 1 pictured below, which includes ID, Date, Value. This dataset has a number of rows with the some duplicate ID's but different dates against them.
Dataset 1

I then have dataset 2 with ID, Date, Empty Column. I want to be able to populate the empty column with the value from dataset 1 that matches the row ID, however is pulled from the row with the closest date before the date specific in dataset 2. (Hope I've explained that well enough). Attached a couple of images for references. Any help would be really appreciated on this one!
Dataset 2

1 Answer
1

For clarity and maintenance, I am doing this in 2 steps. In theory it should be doable in one as described at sql with dates. I have also referred to dates with qoogle query, and looking there one may find simplifications. On the Dataset2 sheet, I added a column D, which may be hidden later if you like, and I named the first sheet Dataset1. in D2, I placed the following formula, which I then dragged down.

=iferror(index(query(Dataset1!$A$2:$C$11,"select MAX(B) where A='"&A2&"' AND B<Date'"&TEXT(DATEVALUE(C2),"yyyy-mm-dd")&"'"),2),"")


The iferror guards against the case where nothing is found, as is the case for ID 2 in your example. index 2, simply picks out the query result as opposed to the header "Max." When you get inside the query you can see it looking at your original data, and finding the largest date where the id's match and the Dataset1 date is less than that in Dataset2 for this line.
Now once you have the date you need (I am assuming there is just one entry corresponding to that date, otherwise you need to handle that), you can query again in B2 (and drag that down as well) with

=iferror(query(Dataset1!$A$2:$C$11,"select C where A='"&A2&"' AND B=Date'"&TEXT(DATEVALUE(D2),"yyyy-mm-dd")&"'"))


Again the iferror is for the same reason (to avoid a bad date format message for the empty one), and now we pick out the value for the item matching the ID and the date we calculated.

That is what your goal was.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.