Generate wide table from lookup values
This should work for you.  At least for the entries in the PowerCurve table that I copied over, it works correctly.  This should get you close.  See comments in the code for more details.

Assistance from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql on how to round to nearest .5/0.

/** FIRST - we setup the data and tables we need.

    Please note that I didn't copy the entire table of dbo.PowerCurve
    across, so my output is not as complete as yours, but this should
    get you close.
    */

DROP TABLE IF EXISTS dbo.Windspeed 
DROP TABLE IF EXISTS dbo.PowerCurve
DROP TABLE IF EXISTS dbo.NameLookup 

CREATE TABLE dbo.Windspeed 
    (
    [TimeStamp] DATETIME NOT NULL
    , [A-004] DECIMAL(5,2) NOT NULL
    , [A-005a] DECIMAL(5,2) NOT NULL
    , [A-006a] DECIMAL(5,2) NOT NULL
    , [E-009] DECIMAL(5,2) NOT NULL
    )

INSERT INTO dbo.Windspeed 
([TimeStamp], [A-004], [A-005a], [A-006a], [E-009])
VALUES ('4/9/2019 00:00:00', 13.09, 13.95, 10.16, 11.71)
    , ('4/9/2019 00:05:00', 12.17, 11.98, 12.54,10.66)

CREATE TABLE dbo.PowerCurve 
    (
    WindSpeedBIN DECIMAL(5,2) NOT NULL
    , Power_Wdspd DECIMAL(10,2) NOT NULL
    , Turb_Num INT NOT NULL
    )

INSERT INTO dbo.PowerCurve 
(WindSpeedBIN, Power_Wdspd, Turb_Num)
VALUES (13.0, 1840, 1)
    , (13.5, 1846.1, 1)
    , (13.0, 1833.1, 2)
    , (13.5, 1846.1, 2)
    , (12.0, 1798.3, 1)
    , (12.5, 1828.5, 1)
    , (12.0, 1713.3, 2)
    , (12.5, 1801.5, 2)

CREATE TABLE dbo.NameLookup 
    (
    TName VARCHAR(10) NOT NULL
    , Tag VARCHAR(10) NOT NULL
    , TNum INT NOT NULL
    )

INSERT INTO dbo.NameLookup
    (TName, Tag, Tnum)
VALUES ('A-004', 'A04', 1)
    , ('A-005a', 'A05', 2)
    , ('A-006a', 'A06', 3)
    , ('A-008a', 'A08', 4)
    , ('E-009', 'E09', 5)


Get the data

/** GET THE DATA
    - CTE_Source - unpivots so we can get the TNum and PowerCurve
    - CTE_Join - looks up the TNum in dbo.NameLocation and dbo.PowerCurve
    - CTE_Pivot - pivots the data back to make the output what we want
    **/

;WITH CTE_Source AS
    (
    SELECT [TimeStamp]
        , [TName]
        , [Value]
    FROM dbo.Windspeed AS W
        UNPIVOT ([Value] FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    )
, CTE_Join AS
    (
    --NOTE rounding answer from https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
    SELECT S.[TimeStamp]
        , S.[TName]
        , S.[Value]
        , CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2)  AS RoundedValue
        , NL.TNum
        , PC.Power_Wdspd
    FROM CTE_Source AS S
        LEFT OUTER JOIN dbo.NameLookup AS NL ON NL.TName = S.TName
        LEFT OUTER JOIN dbo.PowerCurve AS PC ON PC.WindSpeedBIN = CONVERT(DECIMAL(5,2), ROUND(S.[Value] * 2, 0)/2) AND PC.Turb_Num = NL.TNum 
    )
, CTE_Pivot AS
    (
    SELECT [TimeStamp]
        , MAX([A-004]) AS [A-004]
        , MAX([A-005a]) AS [A-005a]
        , MAX([A-006a]) AS [A-006a]
        , MAX([E-009]) AS [E-009]
    FROM CTE_Join AS J
        PIVOT (MAX(Power_Wdspd) FOR TName IN ([A-004], [A-005a], [A-006a], [E-009])) AS P
    GROUP BY [TimeStamp]
    )
SELECT * FROM CTE_Pivot