Revision history [back]

To modify your index match formula with 2 criteria in VBA to include "@" symbol, you can use the following code:

Dim ws As Worksheet
Dim lastRow As Long
Dim nameColumn As Long
Dim dateColumn As Long
Dim nameToFind As String
Dim dateToFind As String

Set ws = ThisWorkbook.Worksheets("Sheet1")      'Modify sheet name as needed
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row      'Assuming data starts from A1
nameColumn = 1      'Modify column number as needed
dateColumn = 2      'Modify column number as needed
nameToFind = "John@Doe"      'Modify name to find as needed
dateToFind = "1/1/2022"      'Modify date to find as needed

Dim result As Variant
result = Application.Index(ws.Range("C2:C" & lastRow), _
                  Application.Match(nameToFind & dateToFind, _ 
                  ws.Range("A2:A" & lastRow) & "@" & _
                  Format(ws.Range("B2:B" & lastRow), "ddmmyyyy"), 0))

MsgBox "Result: " & result

'Note: The above code assumes that the result is in column C. Modify it as per your data.

In the above code, the index match formula with 2 criteria is modified to concatenate the "@" symbol with the date column using the "&" operator in the application.match function. This will help to find the exact match with the criteria including the "@" symbol.