VBA question vlookup with two criterias

[MarioMagnus]

Hi all,
I need help with VBA and to find a value from the sheet, when two criteria are met.
I have a sheet1 build up like structured in such a way that the name is in column A, the date in column B and the calculated total time in column G. There are more than a hundred lines of it. The name can appear several times in column A and of course the date can also appear several times.
What only happens once, however, is that the combination of name and date can only occur once.

Example:
Max 10.10.2020 9.5
Max 11.10.2020 11
Mike 11.10.2020 8.75
Mike 10.10.2020 8.5

I now have a second worksheet, where the name is entered dynamically and also a date. Now I have to find the exact combination from sheet 1, and it has to be written to me in sheet 2.

How can I do something like that - I lack any logic, because with VLOOKUP I can only use one variable.
Is it possible to run VLOOKUP or something similar until the name and then the appropriate date are found?

Thanks and regards, Mario.

 

[DanteAmor]

Try this:

Dante Amor
	A	B	C
1
2	Max	10/10/2020	9.5
3
Sheet2

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT((Sheet1!A2:A20=A2)*(Sheet1!B2:B20=B2)*(Sheet1!C2:C20))

Or this

Dante Amor
	A	B	C
1
2	max	10/10/2020	9.5
Sheet2

Cell Formulas
Range	Formula
C2	C2	=IFERROR(LOOKUP(2,1/((Sheet1!A2:A20=A2)*(Sheet1!B2:B20=B2)),Sheet1!C2:C20), "Not exists")

 

[MarioMagnus]

Hi Dante,
thanks for the reply. How can I attach a sample file, because when I try your formula, I get as a result 0 but it must be 0.5 for example.
Regards, Mario.

 

[MarioMagnus]

MarioMagnus.xlsx
	A	B	C
1	Zeiten
2
3
4	Name	Datum	Zeit gesamt
5			Dauer dezimal
6	Noémie	18/02/2021	6.50
7	Yanik	18/02/2021	6.50
8	Noémie	22/02/2021	4.25
9	Yanik	22/02/2021	4.25
10	Noémie	03/03/2021	5.50
11	Yanik	03/03/2021	5.50
12	Noémie	17/03/2021	6.00
13	Yanik	17/03/2021	6.00
14	Noémie	19/03/2021	2.25
15	Yanik	19/03/2021	3.25
16	Noémie	24/03/2021	6.75
17	Yanik	24/03/2021	6.00
18	Noémie	25/03/2021	11.00
Zeiten

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C6:C1048576	Cell Value	>13	text	NO

MarioMagnus.xlsx
	A	B	C
1	Name	Datum	Zeit
2	Yanik	2/18/21	0.00
3
Sheet3

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT((Zeiten!A5:A200=A2)*(Zeiten!B5:B200=B2)*(Zeiten!G5:G200))

 

[MarioMagnus]

Hi,
I found a solution, that gives me the right value return.

{=INDEX(Zeiten!G5:G200,MATCH(1,(AG5=Zeiten!$A$5:$A$200) * (AG6=Zeiten!$B$5:$B$200),0))}

How can I use this formula within excel VBA?
Thanks, Mario.

 

[DanteAmor]

Check the date format so that you have in both sheets dd / mm / yyyy

varios 09jul2021.xlsm
	A	B	C
1	Name	Datum	Zeit
2	Yanik	18/02/2021	6.5
Sheet2a

Cell Formulas
Range	Formula
C2	C2	=IFERROR(LOOKUP(2,1/((Zeiten!A6:A20=A2)*(Zeiten!B6:B20=B2)),Zeiten!C6:C20), "Not exists")

 

[DanteAmor]

How can I use this formula within excel VBA?

Try this:

Sub Macro1()
  Sheets("Sheet3").Range("E2").FormulaArray = _
    "=INDEX(Zeiten!G5:G200,MATCH(1,(AG5=Zeiten!$A$5:$A$200) * (AG6=Zeiten!$B$5:$B$200),0))"
End Sub

 

[MarioMagnus]

Thanks Dante - i will try this.

 

[MarioMagnus]

Hi Dante,
can you have a look to my code?
When I try to start the macro, I get the error message: "There is a problem with this formula. Not trying to type a formula..."

Private Sub CommandButton_Druck_Click()

    Dim vorlageWs As Worksheet, kinderWs As Worksheet, zeitenWs As Worksheet
    Dim saveLocation As String
    Dim x As Long, y As Long
    Dim intDaysInMonth As Integer
    Dim i As Integer
   
    Dim NachName As Range, VorName As Range
    Dim vNameLastRow As Long, vDateLastRow As Long, vTimeLastRow As Long
    Dim vNameRng As Range, vDateRng As Range, vTimeRng As Range
    Dim xName As String, zelleName As Range
    
   
    Set vorlageWs = Worksheets("Vorlage")
    Set kinderWs = Worksheets("KInder")
    Set zeitenWs = Worksheets("Zeiten")

    Set NachName = kinderWs.Range("A5").CurrentRegion.Columns(2)
    vNameLastRow = zeitenWs.Range("A" & Rows.Count).End(xlUp).Row
    vDateLastRow = zeitenWs.Range("B" & Rows.Count).End(xlUp).Row
    vTimeLastRow = zeitenWs.Range("G" & Rows.Count).End(xlUp).Row
 
    Set vNameRng = zeitenWs.Range("A6:A" & vNameLastRow)
    Set vDateRng = zeitenWs.Range("B6:B" & vDateLastRow)
    Set vTimeRng = zeitenWs.Range("G6:G" & vDateLastRow)
 
    x = 6
    xName = ComboBox_Familie.Value

    For Each zelleName In NachName.Cells
        If zelleName.Value = xName Then
           vorlageWs.Cells(x, 6).Value = zelleName.Offset(0, -1).Value
           x = x + 1
        End If
    Next zelleName
 
    vorlageWs.Cells(4, 6).Value = ComboBox_Familie.Value
    vorlageWs.Cells(2, 6).Value = ComboBox_Monat.Value
    
    'Schleife für die Eingabe der Werte basierend auf den Namen und das Datum
    For y = 20 To 50
        vorlageWs.Range("A" & y).FormulaArray = "=IFERROR(INDEX(vTimeRng,MATCH(1,(vorlageWs.Range('F6')=vNameRng) * (vorlageWs.Range('A' & y)=vDateRng),0)),"")"
        y = y + 1
    Next y
    
    'hier beginnt der bereich für die Druckfunktion
    saveLocation = "/Users/mario.mueller/Dropbox/Tagesfamilie/Verrechnungen/Stundenblatt_" & vorlageWs.Cells(2, 6) & "_" & vorlageWs.Cells(4, 6)
    
    Application.Wait (Now + TimeValue("0:00:01"))
    
'    With vorlageWs.PageSetup
'        .Orientation = xlPortrait
'        .PrintArea = "A1:AB53"
'        .Zoom = False
'        .FitToPagesTall = 1
'        .FitToPagesWide = 1
'    End With
'
'    vorlageWs.ExportAsFixedFormat _
'        Type:=xlTypePDF, _
'        FileName:=saveLocation, _
'        OpenAfterPublish:=True

    Unload Me
    
    Worksheets("Info").Activate
    
End Sub

I hope you can help me with this error.
Thanks, Mario.

 

[DanteAmor]

I'm not sure where you are going to put the formula.
But in your macro you are putting the formula in column A and also in that same column A you are taking the date.

With the following I hope you can adapt it.
Change "E" for the column where you want the formula.
"F6" for the cell where you have the name.
"A" for the column where you have the dates.

    For y = 20 To 50
      vorlageWs.Range("E" & y).FormulaArray = _
        "=IFERROR(INDEX(" & vTimeRng.Address(1, 1, xlA1, 1) & _
        ",MATCH(1,(F6=" & vNameRng.Address(1, 1, xlA1, 1) & _
        ") * (A" & y & "=" & vDateRng.Address(1, 1, xlA1, 1) & "),0)),"""")"
    Next y