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)

6 Answers

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)
1

How about just using:

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

Note the [ and ].

3

Please find the code below for Vlookup:

Function vlookupVBA(lookupValue, rangeString, colOffset) vlookupVBA = "#N/A" On Error Resume Next Dim table_lookup As range Set table_lookup = range(rangeString) vlookupVBA = Application.WorksheetFunction.vlookup(lookupValue, table_lookup, colOffset, False) End Function
1

As Tim Williams suggested, using Application.VLookup will not throw an error if the lookup value is not found (unlike Application.WorksheetFunction.VLookup).

If you want the lookup to return a default value when it fails to find a match, and to avoid hard-coding the column number -- an equivalent of IFERROR(VLOOKUP(what, where, COLUMNS(where), FALSE), default) in formulas, you could use the following function:

Private Function VLookupVBA(what As Variant, lookupRng As Range, defaultValue As Variant) As Variant Dim rv As Variant: rv = Application.VLookup(what, lookupRng, lookupRng.Columns.Count, False) If IsError(rv) Then VLookupVBA = defaultValue Else VLookupVBA = rv End If End Function Public Sub UsageExample() MsgBox VLookupVBA("ValueToFind", ThisWorkbook.Sheets("ReferenceSheet").Range("A:D"), "Not found!") End Sub 
Dim found As Integer found = 0 Dim vTest As Variant vTest = Application.VLookup(TextBox1.Value, _ Worksheets("Sheet3").Range("A2:A55"), 1, False) If IsError(vTest) Then found = 0 MsgBox ("Type Mismatch") TextBox1.SetFocus Cancel = True Exit Sub Else TextBox2.Value = Application.VLookup(TextBox1.Value, _ Worksheets("Sheet3").Range("A2:B55"), 2, False) found = 1 End If 
 Public Function VLOOKUP1(ByVal lookup_value As String, ByVal table_array As Range, ByVal col_index_num As Integer) As String Dim i As Long For i = 1 To table_array.Rows.Count If lookup_value = table_array.Cells(table_array.Row + i - 1, 1) Then VLOOKUP1 = table_array.Cells(table_array.Row + i - 1, col_index_num) Exit For End If Next i End Function