doing a VLOOKUP() in code

[lothario]

I have assigned this function
=VLOOKUP(L21,Sheet1!$F$21:$G$29,2,FALSE)
to cell L21 in Sheet4

It works fine.
It does a lookup in a fixed range.
It does an exact match.
It can be assigned to other cells for doing similar lookups.

In a different spreadsheet, I am trying to do the same but the
conditions are different:
Now the lookup column is J (and not F).
Now the result column is C (and not G).

Also, I need to do this in VB code because:
I cannot assign functions to cells. The user might enter some data in
cells L21, L22, etc.
If the user does not enter some data then this VB code should do this
sort of lookup.

I tried using Macro Recorder but I cannot figure this out.
Can you please help?

 

[Tom Ogilvy]

=Index(Sheet1!$C$21:$C$29,Match(L21,Sheet1!$J$21:$J$29,0),1)

VBA

res = Appliction.Index(worksheets("Sheet1").Range("C21:C29"), _
application.Match(Activesheet.Range("L21"), Worksheets("Sheet1") _
.Range("J21:J29"),0),1)

if not iserror res then
Range("M21").Value = res
End if