Something like the following should get you started, but you can also do this with formulas EXCEPT for needing the results in Column C
============================
Option Explicit
Sub ProcessMain()
Dim rStatic As Range, rMainData As Range, c As Range, r As Range
Dim wbStatic As Workbook, wsStatic As Worksheet
Dim wbMainData As Workbook, wsMainData As Worksheet
Set wbStatic = Workbooks("Static.xlsx")
Set wsStatic = wbStatic.Worksheets("Sheet1")
With wsStatic
Set rStatic = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With
Set wbMainData = Workbooks("Main.xlsm")
Set wsMainData = wbMainData.Worksheets("Sheet1")
With wsMainData
Set rMainData = .Range("A1", .Cells(.Rows.Count, "C").End(xlUp))
End With
For Each c In rMainData.Columns(2).Cells
If c.Value = "SOLID" Then
With rStatic.Columns(3)
Set r = .Find(what:=Left(c.Offset(columnoffset:=1), 2), _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not r Is Nothing Then
c.Offset(columnoffset:=1).Value = r.Offset(columnoffset:=-1).Value
End If
End With
End If
Next c
End Sub
==================================
Using formulas:
MainData
D1: =IF(B1="SOLID",INDEX([Static.xlsx]Sheet1!$B$1:$B$6,MATCH(LEFT(C1,2),[Static.xlsx]Sheet1!$C$1:$C$6,0)),C1)
fill down as needed.
This will return #N/A if there is no match of the first two letters in column C of Main Data, with Column C of Static Data