asked on

Excel lookup - fomrula-macro

Hello,
can you please help, (Formula or Macro)

in Column "AB"
I Need a formula to lookup column "M" in column "C".
if found, bring Value in column A",
If more than one value found, I need all values (separated by underscore, or comma,or ....).

in Column "AC"
I Need a formula to lookup column "M" in column "C".
if found, bring Value in column F",
If more than one value found, I need all values (separated by underscore, or comma,or ....).

in Column "AD"
I Need a formula to lookup column "M" in column "C".
if found, bring Value in column G",
If more than one value found, I need all values (separated by underscore, or comma,or ....).

I have more than 20 thousands rows .

Please see sample attached.
thanks,
Sample.xlsx

Subodh Tiwari (Neeraj)

Try this UDF...

Function GetVlookupData(LookUpValue As Range, LookUpRange As Range, ColumnIndex As Integer)
Dim x
Dim i As Long, c As Long
Dim rng As Range
Dim str As String
c = ColumnIndex
x = LookUpRange.Value
For i = 1 To UBound(x, 1)
   If x(i, 3) = LookUpValue Then
      If str = "" Then
         str = x(i, c)
      Else
         str = str & "_" & x(i, c)
      End If
   End If
Next i
GetVlookupData = str
End Function

Open in new window

Then use it like this......

In AB2

=GetVlookupData($M2,$A$2:$G$27,1)

Open in new window

In AC2

=GetVlookupData($M2,$A$2:$G$27,6)

Open in new window

In AD2

=GetVlookupData($M2,$A$2:$G$27,7)

Open in new window

For details, refer to the attached.

PS. You may change the delimiter as per your requirement in the following line of code
str = str & "_" & x(i, c)
SampleUDF.xlsm

W.E.B

ASKER

Hi Neeraj,
I tried the code, but I'm not getting any results back.
please see sample attached.

thank you.
SampleUDF.xlsm

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you very Much.

Subodh Tiwari (Neeraj)

You're welcome. Glad to help.