如何在不同的列中使用xLookup查找值以检查哪一个是重复的。

huangapple go评论63阅读模式
英文:

How to xLookup values in different columns to check which one is duplicated

问题

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 one column.
If it doesn't exist then move on to lookup in another column.

For example,
if the email is found in A:A then return the said value,
if not found then lookup in B:B and so on.

=XLOOKUP(D2,A:A,A:A,XLOOKUP(D2,B:B,B:B,XLOOKUP(D2,C:C,C:C,"",0,1)))

The native formula
如何在不同的列中使用xLookup查找值以检查哪一个是重复的。

I am looking for ways to loop it for each column.

I tried alternatives such as Match function but failed to loop it.

英文:

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 one column.
If it doesn't exist then move on to lookup in another column.

For example,
if the email is found in A:A then return the said value,
if not found then lookup in B:B and so on.

=XLOOKUP(D2,A:A,A:A,XLOOKUP(D2,B:B,B:B,XLOOKUP(D2,C:C,C:C,"",0,1)))

The native formula
如何在不同的列中使用xLookup查找值以检查哪一个是重复的。

I am looking for ways to loop it for each column.

I tried alternatives such as Match function but failed to loop it.

答案1

得分: 1

以下是代码的翻译部分:

Sub FindAThing()

    Dim emailAddress As String: emailAddress = "joe.bloggs@email.com"
    Dim rg As Range: Set rg = ThisWorkbook.Sheets(1).Range("B:M")

    On Error Resume Next
    Dim lr As String
    lr = rg.Cells.Find(What:=emailAddress _
                    , Lookat:=xlWhole _
                    , LookIn:=xlValues _
                    , searchorder:=xlByColumns _
                    , searchdirection:=xlNext).Address
    If Err.Number > 0 Then
        MsgBox "无法找到电子邮件地址"
    Else
        MsgBox "在单元格中找到电子邮件:" & lr
    End If
    On Error GoTo 0

End Sub
英文:

In response to your comment - perhaps something similar to this

Sub FindAThing()

    Dim emailAddress As String: emailAddress = "joe.bloggs@email.com"
    Dim rg As Range: Set rg = ThisWorkbook.Sheets(1).Range("B:M")

    On Error Resume Next
    Dim lr As String
    lr = rg.Cells.Find(What:=emailAddress _
                    , Lookat:=xlWhole _
                    , LookIn:=xlValues _
                    , searchorder:=xlByColumns _
                    , searchdirection:=xlNext).Address
    If Err.Number > 0 Then
        MsgBox "unable to find email address"
    Else
        MsgBox "Found email in cell " & lr
    End If
    On Error GoTo 0
    
End Sub

答案2

得分: 0

你可以编写一个用户自定义函数(UDF)。
在工作表上使用=FindEmail(D2,$A$5:$D$26)

Public Function FindEmail(EmailAddress As String, SearchRange As Range) As Variant

    'https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Volatile
    '一个挥发函数必须在工作表上的任何单元格发生计算时重新计算。
    '一个非挥发函数仅在输入变量更改时重新计算。
    Application.Volatile False

    'https://learn.microsoft.com/en-us/office/vba/api/excel.range.find
    Dim rFind As Range
    With SearchRange
        Set rFind = .Find(EmailAddress, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False)
        If Not rFind Is Nothing Then
            FindEmail = rFind
            '或者FindEmail = rFind.Address返回地址。
            '或者FindEmail = rfind.Row返回行。
            '或者FindEmail = rfind.Offset(,1)返回下一列。
            '或者FindEmail = rFind.Font.Name返回正在使用的字体名称。
        Else
            '如果未找到值,则返回#N/A - 函数必须返回Variant类型。
            FindEmail = CVErr(xlErrNA)
        End If
    End With

End Function
英文:

You could write a UDF.
Use on a worksheet as =FindEmail(D2,$A$5:$D$26)

Public Function FindEmail(EmailAddress As String, SearchRange As Range) As Variant

    'https://learn.microsoft.com/en-us/office/vba/api/Excel.Application.Volatile
    'A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet.
    'A nonvolatile function is recalculated only when the input variables change.
    Application.Volatile False

    'https://learn.microsoft.com/en-us/office/vba/api/excel.range.find
    Dim rFind As Range
    With SearchRange
        Set rFind = .Find(EmailAddress, .Cells(1, 1), xlValues, xlWhole, xlByRows, xlNext, False)
        If Not rFind Is Nothing Then
            FindEmail = rFind
            'or FindEmail = rFind.Address to return the address.
            'or FindEmail = rfind.Row to return the row.  
            'or FindEmail = rfind.Offset(,1) to return the next column across.  
            'or FindEmail = rFind.Font.Name to return the name of the font being used.
        Else
            'Return #N/A if value not found - function must return Variant for this.
            FindEmail = CVErr(xlErrNA)
        End If
    End With
    
End Function

huangapple
  • 本文由 发表于 2023年3月8日 18:49:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75672054.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定