Ok, the title became too lengthy, but the idea is to try to duplicate the beauty of what this formula does:
1 2 3 4 |
=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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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!