Matching values to a lookup worksheet I have a worksheet that contains values, and I need to match one of the values to a lookup worksheet. This syntax works like it should, however, it takes roughly 10 minutes to fully execute. This could be due to the size of the worksheets: Sheet1 has roughly 18,000 rows and Sheet2 has roughly 20,000 rows.  

Is there any optimization that can take place in this syntax?

Function TwoMatches()
Dim wSheet As Worksheet: Set wSheet = Sheets("Sheet2")
Dim lookupSheet As Worksheet: Set lookupSheet = Sheets("Sheet1")
Dim lr As Long: lr = wSheet.Cells(wSheet.Rows.Count, "A").End(xlUp).Row
Dim destSheet As Worksheet: Set destSheet = Sheets("Sheet2")
Set lookupRange = Sheets("Sheet1").Range("$A:$C")
Dim Found As Range

'Add a column at the beginning to Trim() and Remove Hyphens
Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'Add Clean Sheet1 Name As Header Text
ActiveWorkbook.Sheets("Sheet2").Range("A1").Value = "Clean Sheet1 Name"

'This is the worksheet formula that I want to use for the used range
With wSheet
    lr = .Cells(.Rows.Count, "D").End(xlUp).Row
    form = "=TRIM(SUBSTITUTE(D2,""-"", """"))"
    .Range("A2:A" &amp; lr).Formula = form
End With

'Get the Last Column With Data
ActiveWorkbook.Sheets("Sheet2").Range("P1").Value = "LookupVal"

For i = Range("A" &amp; Rows.Count).End(3).Row To 2 Step -1
    'Pulling value where column O is not null
    If Not IsEmpty(Cells(i, "O").Value) Then
    With Sheets("Sheet1")
        'Filter criteria one is where column B IS NOT NULL
        .Range("$A:$C").AutoFilter Field:=2, Criteria1:="&lt;&gt;"

        Set Found = .Range("A:A").Find(What:=wSheet.Cells(i, "A").Value, _
                               LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False)

        If Not Found Is Nothing Then
            wSheet.Cells(i, "P").Value = Found.Offset(, 2).Value
        End If
    End With
    'Pulling value where column O is null
    Else
    With Sheets("Sheet1")
        'Filter criteria one is where column B IS NULL
        .Range("$A:$C").AutoFilter Field:=2, Criteria1:="="
        Set Found = .Range("A:A").Find(What:=wSheet.Cells(i, "A").Value, _
                               LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                               MatchCase:=False)

        If Not Found Is Nothing Then
            wSheet.Cells(i, "P").Value = Found.Offset(, 2).Value
        End If
    End With
    End If
Next i
'Removing the column added for the Clean Match
Range("A:A").Delete
End Function
General points


both wSheet and destSheet points to "Sheet2", I suppose you don't need both
while you can save a few lines using :, it's generally considered to be a bad practice to use them.
functions are generally more or less portable, hardcoding sheet names in them makes it impossible. You could pass sheet names as arguments
lookUpRange is not declared


You should always use Option Explicit in your code

Dim Found As Range - it's name suggests it's a boolean, not a range
lr = wSheet.Cells(wSheet.Rows.Count, "A").End(xlUp).Row - you set the value at the beginning, but never use it, just overwrite
ActiveWorkbook.Sheets("Sheet2").Range("P1").Value = "LookupVal" You've declared variables for Sheet2, why don't use them?
... and a lot of other similar issues


General performance


you can use these at the beginning of your codeto speed up your code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
to perform calculations after inserting the formulas use
Application.calculate


Specific suggestion

.Range("$A:$C").AutoFilter Field:=2, Criteria1:="&lt;&gt;"
Set Found = .Range("A:A").Find(What:=wSheet.Cells(i, "A").Value, _
    LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _                                                                 
    MatchCase:=False)
If Not Found Is Nothing Then
    wSheet.Cells(i, "P").Value = Found.Offset(, 2).Value
End If


It's difficult to give the best solution without looking your data, but here I'd definitely use some formula (Index / match / row / ...) instead of filtering the data thousands of times. Or at least sort data to avoid some of the filtering.