CODEWITHSUNDEEP
excelvbavlookup
Junior1988
Junior1988

Reputation: 23

Vlookup using VBA Function

I have two spreadsheets:

Spreadsheet Users:

ID                     NAME
jg1988            Junior Gomez
rs1772            Rose Mills

Spreadsheet DATAANDLINKS:

USERS
Jeff Connor,Rose Mills,Junior Gomez,Michael smith

I need a Vlookup Function to look at users and get the id of the matches.

In this case:

rs1772,jg1988

Upvotes: 0

Views: 76

Answers (1)

CDP1802
CDP1802

Reputation: 16174

With a user defined function it is possible. Locate in a module and use as =A_Lookup(A6,"Users","B","A") where A6 is the string of names, "Users" is the lookup sheet, "B" is the column with names, "A" the IDs.

Function A_Lookup(names As String, sht As String, colName As String, colID As String) As String

    Dim rngName As Range, rngID As Range
    With ThisWorkbook.Sheets(sht)
        Set rngName = .Range(colName & ":" & colName)
        Set rngID = .Range(colID & ":" & colID)
    End With

    Dim ar As Variant, s As String, n As Integer, i As Integer
    ar = Split(names, ",")
    s = ""
    On Error Resume Next
    For n = 0 To UBound(ar)
        i = 0
        If Len(ar(n)) > 0 Then
            i = WorksheetFunction.Match(CStr(ar(n)), rngName, 0)
            If i > 0 Then
                If s <> "" Then s = s & ","
                s = s & rngID.Cells(i, 1)
            End If
        End If
    Next
    On Error GoTo 0
    A_Lookup = s

End Function

Upvotes: 1

Related Questions