Drop Table #t
Create Table #t (
	Firm varchar(10),
	Yr int,
	Rev int,
	Primary Key (Firm, Yr)
)

Insert Into #t Select 'FirmA', 1991, 125
Insert Into #t Select 'FirmA', 1992, 130
Insert Into #t Select 'FirmA', 1993, 160
Insert Into #t Select 'FirmB', 1991, 150
Insert Into #t Select 'FirmB', 1992, 175
Insert Into #t Select 'FirmC', 1992, 130
Insert Into #t Select 'FirmC', 1993, 120
Insert Into #t Select 'FirmD', 1990, 100 -- Test Firm with no Rev for 91-93

-- Make a bunch of copies
Insert Into #t
Select Firm + CONVERT(varchar,number), Yr, Rev 
From #t A, (Select number From master..spt_values Where type = 'P') B


Select * 
From #t

Declare @rs int, --Range Start
		@re int  --Range End

Set @rs = 1991
Set @re = 1993


	Select 
		Firm,
		Yr,
		Rev
	From
		(
		Select 
			A.Firm, 
			B.Yr,
			A.Rev,
			r = ROW_NUMBER() Over(Partition By A.Firm, B.Yr Order By abs(A.Yr - B.Yr))
		From #t A
		Cross Join (Select Yr=number From master..spt_values Where number between @rs and @re) B
		Where A.Yr between @rs and @re
		) Z
	Where r = 1

-- table variable
declare @data as table (
 FIRM nvarchar(100), YEAR int, REVENUE float)
)

-- sample data
insert into @data(FIRM, YEAR, REVENUE)
Select a.FIRM, a.YEAR, a.REVENUE from [excel table] as a

-- SQL
select sq.FIRM, sq.yr, coalesce(d.revenue, closest_revenue.closest_revenue) as revenue

from (
    -- generate a result set that matches firms to years
    select distinct d.FIRM, yrs.yr
    from @data AS d
    cross apply (values(2004),(2005),(2006), (2007), (2008), (2009), (2010)) yrs(yr)
) AS sq
left outer join @data d on sq.FIRM=d.FIRM and sq.YEAR=d.YEAR

outer apply (
 -- look up the closest revenue
 -- how do I deal with ties?
 select top 1 d_closest.revenue as closest_revenue
 from @data as d_closest
 where sq.FIRM=d.FIRM
 order by abs(sq.yr - d_closest.DATAYEAR)
) AS closest_revenue

order by sq.ORG_KEY, sq.SEGMENT_KEY, sq.yr

-- temp table
Create Table #firms table (
	FirmId int identity(1,1)
	FIRM nvarchar(100),
	Primary Key (FirmId)
)

Create Table #data table (
	FirmId int,
	FIRM nvarchar(100), 
	Yr int, 
	REVENUE float
)

-- sample data
insert into #firms (FIRM)
Select distinct a.FIRM from [excel table] as a Order By A.Firm


insert into #data(FIRM, Yr, REVENUE)
Select 
	FirmId = (Select FirmId From #firms Where Firm = A.Firm),
	a.Firm,
	a.Yr, 
	a.REVENUE 
from [excel table] as a


Select 
	Z.FirmId,
	Y.Firm,
	Z.Yr,
	Z.Rev
From
	(
	Select 
		A.FirmId, 
		B.Yr,
		A.Rev,
		r = ROW_NUMBER() Over(Partition By A.FirmId, B.Yr Order By abs(A.Yr - B.Yr))
	From #data A
	Cross Join (values(2004),(2005),(2006), (2007), (2008), (2009), (2010)) B(Yr)
	Where A.Yr between 2004 and 2010
	) Z
Inner Join #firms Y
On Z.FirmId = Y.FirmId
Where r = 1
Order By Z.FirmId, Z.Yr

(18441 row(s) affected)
Table '#t_____000000006850'. Scan count 1, logical reads 69, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 125 ms,  elapsed time = 446 ms.