Lookup target based on date

Hi everyone,

I am building a report to show whether business Actions are on-track or off-track. I am unsure how to lookup the target for each Action based on today’s date.

In one query, I have a list of Actions and the percent they have actually been Completed as at today:

In a second query, I have the amount we expect the action to be completed by at the end of each quarter. The same Actions are repeated across several rows to show their target at the end of each quarter:

Other important factors to note:

• Not all Actions listed in query 1 have targets set for them in query 2, so they do not appear in query 2 (such as Action2).

• Some Actions have targets listed in query 2, but no target for the current quarter (such as Action3). In these cases, the target should be the last target listed.

How can I use these two tables to determine what the current target for each action is?