Search a value in multiple columns and return a value

[david427]

Hello,

Im trying to match a value on multiple columns and return an specific value. The problem is, ive already tried all lookup, index and match formulas and no one worked for this.

Example of what im trying to do: i have like 5 columns of massive information incluiding 3 columns of MAILS, i want to lookup in those mails and return a specific ID from the Column A.

Thanks in advance for the help

 

[mumps]

I'm not sure about a formula but if you are open to using a macro, I may have a solution for you. Would you have more than one lookup email in column F?

 

[david427]

Hi Mumps,

No, only 1 email in column F.

Im able to use macros =)

 

[david427]

Hi Mumps,

No, only 1 email in column F.

Im able to use macros =)

I meaan, in colum F will be a lot of mails, but only oe per CELL.

 

[mumps]

Try:

Sub ReturnValue()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Range, email As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each email In Range("F2", Range("F" & Rows.Count).End(xlUp))
        Set fnd = Range("B:D").Find(email.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            email.Offset(, 1) = Range("A" & fnd.Row)
        End If
    Next email
    Application.ScreenUpdating = True
End Sub