• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

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
0
W.E.B
Asked:
W.E.B
  • 3
  • 2
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
 
W.E.BAuthor Commented:
Hi Neeraj,
I tried the code, but I'm not getting any results back.
please see sample attached.

thank you.
SampleUDF.xlsm
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's because your lookup value is a number and the lookup column is text.

Try the below code.....
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 CStr(x(i, 3)) = CStr(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

0
 
W.E.BAuthor Commented:
Thank you very Much.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now