Return Value of Previous Row Column

This is actually a simplified gaps and islands problem, so you can avoid the recursion altogether:

WITH data AS (
    SELECT  *
    FROM    (
        VALUES  (1, 123, 1, 123)
        ,   (2, 456, 1, 456)
        ,   (3, 789, 2, 456)
        ,   (4, 147, 2, 456)
        ,   (5, 258, 3, 258)
        ,   (6, 852, 4, 258)
        ,   (7, 963, 2, 258)
        ,   (8, 213, 1, 213)
    ) t (Record_Number,Col1,Col2,[Col3 expected])
)
SELECT  *
,   MAX(CASE WHEN col2 IN(1,3) THEN col1 END) OVER(PARTITION BY grouping) AS [Col3 actual]
FROM    (
    SELECT  *
    ,   SUM(CASE WHEN col2 IN(1,3) THEN 1 ELSE 0 END) OVER(ORDER BY Record_Number rows unbounded preceding) AS grouping
    FROM    data
    ) x

CASE WHEN col2 IN(1,3) THEN 1 ELSE 0 END calculates the desired values in form of flags, which are the summarized ordered by Record Number. This creates a rolling group counter.

Then by taking the maximum of desired value per group (MAX(CASE WHEN col2 IN(1,3) THEN col1 END) OVER(PARTITION BY grouping)) you get the result you're looking for.

Output:

Here's a way to do it using a subquery:

CREATE TABLE #ver
(
    Record_Number INT
  , Col1 INT
  , Col2 INT
);

INSERT INTO #ver
(
    Record_Number
  , Col1
  , Col2
)
VALUES
(1, 123, 1)
, (2, 456, 1)
, (3, 789, 2)
, (4, 147, 2)
, (5, 258, 3)
, (6, 852, 4)
, (7, 963, 2)
, (8, 213, 1);

SELECT ov.Record_Number
    , ov.Col1
    , ov.Col2
    , CASE
        WHEN ov.Col2 IN ( 1, 3 ) THEN
            ov.Col1
        ELSE (
            SELECT TOP 1
                iv.Col1
            FROM #ver iv
            WHERE iv.Record_Number < ov.Record_Number
            AND iv.Col2 IN( 1, 3)
            ORDER BY iv.Record_Number DESC
        )
        END AS Col3
FROM #ver ov;

If you are using SQL Server 2022 then you can use LAST_VALUE with a conditional expression while ignoring nulls e.g.

SELECT *
    , CASE WHEN Col2 IN (1, 3) THEN Col1
    ELSE
        LAST_VALUE(CASE WHEN Col2 IN (1, 3) THEN Col1 ELSE NULL END) IGNORE NULLS
        OVER (ORDER BY Record_Number ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    END AS Col3  
FROM tbl1
ORDER BY Record_Number;

Returns:

db<>fiddle

With inspiration from this answer

I do not believe there is any way to reference another row using a window function from within a calculated column definition. However, you can wrap the operation up in a user-defined function using a TOP 1 lookup.

CREATE FUNCTION GetCol3 (@Record_Number INT, @Col1 INT, @Col2 INT)
RETURNS INT
AS
BEGIN
    RETURN CASE 
        WHEN @Col2 in (1, 3) THEN @Col1
        ELSE (
            SELECT TOP 1 Col1
            FROM tbl1
            WHERE Record_Number < @Record_Number
            AND Col2 in (1, 3)
            ORDER BY Record_Number DESC
        )
    END
END

Based on your expected results, I also added the condition AND Col2 in (1, 3) to the above.

We have a chicken-and-egg problem between the table and function definitions, so the table will first need to be defined without the computed column, the function created, and then the computed column added.

CREATE TABLE tbl1 (
    Record_Number INT,
    Col1 INT,
    Col2 INT
)

-- Define dbo.GetCol3 as above

ALTER TABLE tbl1
    ADD Col3 AS dbo.GetCol3(Record_Number, Col1, Col2)

SELECT * FROM tbl1

Results:

See this db<>fiddle for a demo.

This was my first answer, but I got an easier one at the end with Transact-SQL.

Here was my first idea with cascading CTEs, that is solveable, but needs lot of brain and solution is quite complex, and not yet done to the end:

What about this:

WITH groupLimits AS ( -- all places where neighboring rows change (col2 in (1,3) vs. col2 not in (1,3))
    SELECT A.Record_Number arn, A.Col1 ac1, A.Col2 ac2,
           B.Record_Number brn, B.Col1 bc1, B.Col2 bc2,
           CASE WHEN A.Col2 IN (1, 3) THEN 1 ELSE 0 END ain,
           CASE WHEN B.Col2 IN (1, 3) THEN 1 ELSE 0 END bin
    FROM tbl1 A
    JOIN tbl2 B
      ON A.Record_Number + 1 = B.Record_Number
         AND (
               (A.Col2 IN (1, 3) AND B.Col2 NOT IN (1, 3))
               OR
               (A.Col2 NOT IN (1, 3) AND B.Col2 IN (1, 3))
             )
    order by A.Record_Number
), groups as (
   select ...
   from groupLimits C
   join groupLimits D
     on ...
...
select ...
...

With above groupLimits you get the idea where groups of (neighbor) rows, that have the same condition, either Col2 IN (1, 3) or Col2 NOT IN (1, 3) end (even if the group contains only 1 row).

The (neighboring) entries of groupLimits could be joined again with each other to get the minimum and maximum record_number of a group of tbl1 entries, that have all either Col2 IN (1, 3) or Col2 NOT IN (1, 3) (ain, bin can help). Perhaps you have to add an additional entry for first and last of record_number in tbl1.

Then you can calculate the col3 value for a group of rows with Col2 NOT IN (1, 3) (e. g. record_number 3 and 4), that is the value of record_number 2 (before 3) = 456. The col3 value for all records in a group with rows with Col2 IN (1, 3) is anyway its col1 value and easy to handle.

Are these enough ideas/hints to solve it?

Here a working solution with an online version:

It simply loops over the records from start and works nearly identically like a human would do.

If it comes to a record where Col2 IN (1,3) (then @Inside = 1), it copies the value from Col1 to Col3 (and updates the @lastCol3). In the other case it just copies the @LastCol3 into Col3.

-- INIT database
CREATE TABLE tbl1 (
  Record_Number INT,
  Col1 INT,
  Col2 INT,
  Col3expected INT,
  Col3calculated INT
);

INSERT INTO tbl1(Record_Number, Col1, Col2, Col3expected) 
VALUES 
(1, 123, 1, 123),
(2, 456, 1, 456),
(3, 789, 2, 456),
(4, 147, 2, 456),
(5, 258, 3, 258),
(6, 852, 4, 258),
(7, 963, 2, 258),
(8, 213, 1, 213);

DECLARE @lastCol3 as int;
DECLARE @RecNumber as int;
DECLARE @Col1 as int;
DECLARE @Inside as int;
select @lastCol3 = null;

DECLARE tbl1_Cursor CURSOR FOR
  select record_number, col1, case when col2 in (1,3) then 1 else 0 end 
  from tbl1 
  order by record_number;
OPEN tbl1_Cursor;

FETCH NEXT from tbl1_Cursor into @RecNumber, @Col1, @Inside;

WHILE @@FETCH_STATUS = 0
   BEGIN 

      IF @Inside = 1 -- means same as "Col2 IN (1, 3)"
         BEGIN
            update tbl1 set col3calculated = @Col1
            where record_number = @RecNumber;
            select @lastCol3 = @Col1; 
         END
      ELSE 
        BEGIN
            update tbl1 set col3calculated = @lastCol3
            where record_number = @RecNumber;
        END

     FETCH NEXT from tbl1_Cursor into @RecNumber, @Col1, @Inside;
   END;

CLOSE tbl1_Cursor;
DEALLOCATE tbl1_Cursor;
GO

SELECT *
FROM tbl1
WHERE col3expected = col3calculated
ORDER BY record_number

And the output: