Problem lookup multiple criterias

[Mathias Bech Jensen]

Hey,

I have a problem at Work and I have spent 5 hours of searching the web now without any luck. So I will try my luck in here. BTW my VBA skills are relatively basic .

I want a vlookup code that can handle multiple criterias in VBA. I have tried the index/match function with inspiration from the link below, but I cannot figure out to transfer this to a VBA code:

http://www.exceltactics.com/vlookup...ch/4/#Using-INDEX-and-MATCH-with-Two-Criteria

My index array: Sheets("sheet1").Range("A:D")
1. criteria: 2015
2. criteria: 47
3. criteria: 39970002

When these 3 criterias are fulfilled, the returned value must be found in column D. I have tried this:

x = Application.WorksheetFunction.Index(Sheets("sheet1").Range("A:D"), _
  Application.WorksheetFunction.Match(1, (Sheets("sheet1").Range("A:A") = 2015) _
  * (Sheets("sheet1").Range("B:B") = 47) _
  * (Sheets("sheet1").Range("C:C") = 39970002),0), 4)

Regards,

Mathias

 

[Marc L]

Hi !

Without a workbook attachment,
I just think about a filter on each column : no need a code …
​

 

[Mathias Bech Jensen]

Hi !

Without a workbook attachment,
I just think about a filter on each column : no need a code …
​

Hi Marc

Thank you!

I need this to run automatically via macro for the users at work. I have attached a piece of my workbook and you will find the code under the module "Indlaes_udbyttedata".

Hope this may help you helping me

/Mathias

 

[Debaser]

If there will only be one match for your criteria, you could use this:

Sub formulas()
    Dim lRow                  As Long
    Dim n                     As Long
    Dim rngTable              As Range
   
    Set rngTable = Sheets("kilo").Range("A1").CurrentRegion

    lastrow = Sheets("data").Cells(Rows.Count, "A").End(xlUp).Row

    For n = 2 To lastrow

        'Lookup multiple criterias to return actual kilo

        With Sheets("data").Cells(n, 1)
            lRow = Application.SumProduct(Application.CountIfs(.Cells(1), rngTable.Columns(1), _
                                  .Offset(, 1), rngTable.Columns(2), _
                                  .Offset(, 3), rngTable.Columns(3)), _
                                  Evaluate("ROW(" & rngTable.Columns(1).Address & ")"))
            If lRow > 0 Then .Offset(0, 4).Value = Sheets("kilo").Cells(lRow, "D")
        End With
    Next n
End Sub

 

[Mathias Bech Jensen]

If there will only be one match for your criteria, you could use this:

Sub formulas()
    Dim lRow                  As Long
    Dim n                     As Long
    Dim rngTable              As Range
  
    Set rngTable = Sheets("kilo").Range("A1").CurrentRegion

    lastrow = Sheets("data").Cells(Rows.Count, "A").End(xlUp).Row

    For n = 2 To lastrow

        'Lookup multiple criterias to return actual kilo

        With Sheets("data").Cells(n, 1)
            lRow = Application.SumProduct(Application.CountIfs(.Cells(1), rngTable.Columns(1), _
                                  .Offset(, 1), rngTable.Columns(2), _
                                  .Offset(, 3), rngTable.Columns(3)), _
                                  Evaluate("ROW(" & rngTable.Columns(1).Address & ")"))
            If lRow > 0 Then .Offset(0, 4).Value = Sheets("kilo").Cells(lRow, "D")
        End With
    Next n
End Sub

I reduced and changed my data, but the code Works brilliantly!

Thank you very much Debaser - I wish you pleasant Christmas Holidays!