Here is something quick and dirty. This loads both ranges into an Array.
Because there is a gap in Column C, this first fills it with the value 1.
It then uses the Correl function to see if both arrays are equal. If equal
(1) then the solution array (Sol1 & Sol2) are given the value of B.
Again, just quick and dirty. Just a general idea you will have to adjust.
HTH.

Sub Demo()
Dim s1
Dim s2
Dim a
Dim b
Dim Sol1()
Dim Sol2()

s1 = Sheets("Sheet1").[B1:H10]
s2 = Sheets("Sheet2").[B1:H10]

ReDim Sol1(1 To UBound(s1))
ReDim Sol2(1 To UBound(s2))

With WorksheetFunction

'// Fix gap in column C with '1'
For a = 1 To UBound(s1, 1)
s1(a, 2) = 1
Next a
For a = 1 To UBound(s2, 1)
s2(a, 2) = 1
Next a

'// Continue w/ loops
For a = 1 To UBound(s1, 1)
For b = 1 To UBound(s2, 1)

If .Correl(.Index(s1, a, 0), .Index(s2, b, 0)) = 1 Then
Sol1(a) = b
Sol2(b) = b
Exit For
End If
Next b
Next a

Sheets("Sheet1").[A1].Resize(UBound(s1, 1)) = .Transpose(Sol1)
Sheets("Sheet2").[A1].Resize(UBound(s2, 1)) = .Transpose(Sol2)
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Lawlera" wrote in message
...
Thanks for the input but I need VBA because the match may not occur on the
corresponding row in sheet2 and consequently I need to scan all entries
lokking for a match.