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)

score 47 · Accepted Answer · answered Apr 06 '11 at 14:01

47

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)

answered Apr 06 '11 at 14:01

Ben Hoffstein

102,129
8
104
120

11

If the lookup fails it will throw an error: however if you drop the "WorksheetFunction" it will not, but you can test the outcome using IsError(result). You'd need to declare result as variant though. – Tim Williams Apr 06 '11 at 21:10

score 18 · Answer 2 · answered Apr 06 '11 at 16:40

18

How about just using:

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

Note the [ and ].

answered Apr 06 '11 at 16:40

Oneide

1,020
8
12

4

The information above is based upon this answer -> [Hidden features of vba](http://stackoverflow.com/questions/1070863/hidden-features-of-vba) – Oneide Apr 06 '11 at 16:56
3

What if you need part of this statement to be dynamic? For example, the AF20 could be AF99 or AF10. Normally with a string, you could use something like: "VLOOKUP(DATA!AN2, DATA!AA9:AF" & lastrow & ", 5, FALSE)". That doesn't seem to work when using the square brackets. – Noah Jul 22 '13 at 00:30
3

@Noah I think the accepted answer is what you need if you have something dynamic in mind. – Oneide Jul 23 '13 at 01:32

score 7 · Answer 3 · edited Feb 20 '13 at 04:05

7

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

edited Feb 20 '13 at 04:05

Unni Kris

3,081
4
35
57

answered Feb 20 '13 at 03:32

Alejandro Sardi

87
1
1

This works perfectly for me as the VLOOKUP issues bothers me couple hours. This also works for lookup up on other sheet, thanks for the sharing! – Jackie Yeh Dec 22 '16 at 06:25

score 2 · Answer 4 · answered May 15 '18 at 21:10

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

score -1 · Answer 5 · edited Dec 08 '16 at 11:09

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

score -2 · Answer 6 · answered Oct 31 '16 at 12:10

        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

Writing a VLOOKUP function in vba

6 Answers6

Linked