CODEWITHSUNDEEP
excelvbaxlookup
Khai
Khai

Reputation: 27

VBA/Xlookup with multiple criterias

I am trying to do an xLookup on email addresses in different columns to check which one is duplicated.

The idea is to lookup an email address in a column, if it doesn't exist then move on to lookup in another column.

For example, if Range("E1:E6").Value is empty then lookup in range b and return any email address duplicates in that range. If not, move on to range c and so on.

The native formula:
enter image description here

I'm open to any alterative that achieves the same goal.

Sub lookup()
Dim result AS Boolean
result = Range("E2:E6").Value 
Range("E2:E6").Value = Application.XLookup(Range("D2:D6"), Range("A:A"), Range("A:A"))

If IsError(Range("E2:E6")).Value Then 
    Range("E2:E6").Value = Application.XLookup(Range("D2:D6"), Range("B:B"), Range("B:B")) 

Else 
    Range("E2:E6").Value = Application.XLookup(Range("D2:D6"), Range("C:C"), Range("C:C"))
End If 

End Sub

Upvotes: 2

Views: 470

Answers (1)

k1dr0ck
k1dr0ck

Reputation: 1215

example: find in column A-B-C from list in column D and put in column E

Sub FindDuplicate()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, x As Long: x = 2
    Dim searchValue As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    Dim lastRowA As Long
    Dim lastRowB As Long
    Dim lastRowC As Long
    Dim highestLastRow As Long
    
    ' Initialize variables
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row ' Get the last row in column D
    
    lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    lastRowC = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    ' Find the highest last used row among columns A, B, and C
    highestLastRow = Application.WorksheetFunction.Max(lastRowA, lastRowB, lastRowC)
    
    ' Loop through the values in column D
    For i = 1 To lastRow
        searchValue = ws.Cells(i, "D").Value ' Get the value in column D
        Set searchRange = ws.Range("A2:C" & highestLastRow) ' Set the search range to columns A-C
        
        ' Use the Find method to search for the value in columns A-C
        Set foundCell = searchRange.Find(searchValue, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' If a match is found, enter the value in column E
        If Not foundCell Is Nothing Then
            ws.Cells(x, "E").Value = foundCell
            x = x + 1
        End If
    Next i
End Sub

Upvotes: 1

Related Questions