How do I select MAX/MIN from multiple tables, along with other columns from MIN/MAX row
As a another option

WITH Ago AS (
    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_Last1]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_2_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_3_Ago]

    UNION

    SELECT  UCC12UPCCode, TotalUnits, Date
    FROM    [CACTUS].[dbo].[RTG_Lookup2_4_Ago]
    ),
    Ranked AS (
    SELECT *, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits, Date) LowUnitRank, 
        ROW_NUMBER() OVER (PARTITION BY UPC ORDER BY TotalUnits DESC, Date) HighUnitRank
    FROM Ago
    )
SELECT L4.UCC12UPCCode,
    MAX(CASE WHEN LowUnitRank = 1 THEN TotalUnits END) LowTotalUnits,
    MAX(CASE WHEN HighUnitRank = 1 THEN TotalUnits END) HighTotalUnits,
    MAX(CASE WHEN LowUnitRank = 1 THEN Date END) LowUnitDate,
    MAX(CASE WHEN HighUnitRank = 1 THEN Date END) HighUnitDate
FROM RTG_LOOKUP_LAST4 L4
    INNER JOIN Ranked r ON L4.UCC12UPCCode = r.UCC12UPCCode
GROUP BY L4.UCC12UPCCode;


I've done this using a CTE query as I find it easier to follow and explain.  

The first CTE is just your Ago data.

The second CTE ranks each row of the Ago data using ROW_NUMBER based on the ordering of the TotalUnits.  I have include the Date in the order so that if there are duplicate TotalUnits the first occurrence will be picked.

In the final query we are using CASE statements to pivot the data and aggregating the result for each UCC12UPCCode with MAX to create a single row for each UCC12UPCCode.

This query can be run over multiple UCC12UPCCode's