VBA – VLookup with Multiple Criteria in Excel without Excel Formula but with VBA

Ok, the title became too lengthy, but the idea is to try to duplicate the beauty of what this formula does:

=INDEX(myTable[#All],MATCH(1,
                      (myTable[[#All],[Profit]]=C9)*
                      (myTable[[#All],[Currency]]=D9)*
                      (myTable[[#All],[Value]]=B9),0),1)

And this is the cell of the formula above:

Anyway, the strange thing was that we have beautiful way to multiply boolean arrays in Excel and in VBA we have to go around it quite a lot. With plenty of code, actually. Asked a bit in StackOverflow, and some of the answers were actually pretty neat, but at the end I have decided to hardcode 2 functions instead of doing endless debugging after 3 months, if something goes south.

This is what I ended with:

Function GetLookupDataTriple(wks As Worksheet, tableName As String, lookIntoColumn As String, myArray As Variant) As Variant
    
    Dim lo As ListObject
    Set lo = wks.ListObjects(tableName)
    
    Dim i As Long
    For i = 2 To lo.ListColumns(myArray(0)).Range.Rows.Count
        If lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1) Then
            If lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3) Then
                If lo.ListColumns(myArray(4)).Range.Cells(RowIndex:=i) = myArray(5) Then
                    GetLookupDataTriple = lo.ListColumns(lookIntoColumn).Range.Cells(RowIndex:=i)
                    Exit Function
                End If
            End If
        End If
    Next i
    
    GetLookupDataTriple = -1
    
End Function

Function GetLookupDataDouble(wks As Worksheet, tableName As String, lookIntoColumn As String, myArray As Variant) As Variant
    
    Dim lo As ListObject
    Set lo = wks.ListObjects(tableName)
    
    Dim i As Long
    For i = 2 To lo.ListColumns(myArray(0)).Range.Rows.Count
        If lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1) Then
            If lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3) Then
                GetLookupDataDouble = lo.ListColumns(lookIntoColumn).Range.Cells(RowIndex:=i)
                Exit Function
            End If
        End If
    Next i
    
    GetLookupDataDouble = -1
    
End Function

It is pretty lengthy and probably one of the reasons VBA people are hated all over the world, but it works. Anyway, take a look at the StackOverflow question, some of the answers are decent and you will learn something from them.

Enjoy!