VBA/Xlookup with multiple criterias advice

Question

Hi I am trying to do a xLookup on emails in different columns to check which one is duplicated.

The idea is to lookup an email in 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 dupes in that range. If not, move on to range c and so on.

Here is the native formula for easier reference: enter image description here

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

Here is the vba code

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

I expect the code to perform the lookup then if it's not found, move on to other columns to lookup

Thanks everyone

you cant do this: `If IsError(Range("E2:E6")).Value` you will need to loop and test each cell. — Scott Craner, Mar 07 '23 at 22:53
would there be a alternative to this then? can we use Match instead of xlookup function, becuase the point is to automate the process of looking up each cell. — Khai, Mar 07 '23 at 23:15
How do you propose to loop it? Im a little new to vba as well. Sry — Khai, Mar 07 '23 at 23:21

score 0 · Answer 1 · answered Mar 09 '23 at 04:51

0

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

answered Mar 09 '23 at 04:51

k1dr0ck

1,043
4
13

oh wow tremendous that works thank you!, can you hellp me explain why is finding highest last row used needed? also for x=2? (ln 4). eseentially if you don't find the highest last row used, the code still works. Thank you for your detailed instruction though! – Khai Mar 09 '23 at 14:17
also for the search range, how do we set it to search for non-adjacent columns? for example, search in column, A;A, C:C, AC:AC – Khai Mar 09 '23 at 15:58
@Khai from your example column A-B-C does not have the same no. of rows with values thats why to be dynamic i used max function, to use non adjacent columns for ex: ws.Range("A2:B10,D5:F20,H2:H15") – k1dr0ck Mar 10 '23 at 02:42
x=2 is for column E so the output will start same row as column A to D – k1dr0ck Mar 10 '23 at 02:50
thank you! can you help me explain why we need to set the last row for col A,B,C ? is it because so the code knows to search values until the last row in A,B,C and not go beyond that where there is data? because i tried removing those and in the searchrange i just set the fixed range (e.g., A2:A302) and it still comes out fine. Thank you for your help : ) – Khai Mar 14 '23 at 16:10
@Khai it just sets the range of cells to iterate in – k1dr0ck Mar 15 '23 at 02:57

VBA/Xlookup with multiple criterias advice

1 Answers1