Writing a VLOOKUP function in vba

Question

I'm trying to lookup a value on a spreadsheet within a table array using the VLOOKUP function in my vba code. I don't know how to write it correctly.

Here is the normal VLOOKUP formula with all the references:

=VLOOKUP(DATA!AN2,DATA!AA9:AF20,5,FALSE) 

Answer 1

Have you tried:

Dim result As String  Dim sheet As Worksheet  Set sheet = ActiveWorkbook.Sheets("Data")  result = Application.WorksheetFunction.VLookup(sheet.Range("AN2"), sheet.Range("AA9:AF20"), 5, False)