Need help with a formula

[Can-Ann]

I have two worksheets (Excel 2007)

I want to use the value of sheet1 columns b5:b99 to look up a table in
sheet2 columns c9:c99 and if there is a match return sheet2 column R value to
sheet1 column R.

I hope someone can understand this- any help would be appreciated - thanks
in advance.

 

[dmoney]

put this formula in sheet1 r5 and fill down to r99

=VLOOKUP(B5,Sheet2!$C$9:$R$99,16,FALSE)

 

[JLGWhiz]

If you wand VBA, then

Sub getColR()
Dim c As Range, f As Range
For Each c In Sheets("Sheet1").Range("B5:B99")
Set f = Sheets("Sheet2").Range("C9:C99") _
.Find(c.Value, LookIn:=xlValues)
If Not f Is Nothing Then
Sheets("Sheet2").Range("R" & f.Row).Copy _
Sheets("Sheet1").Range("R" & c.Row)
End If
Next
End Sub