Solved

Excel lookup - fomrula-macro

Posted on 2016-08-23
5
81 Views
Last Modified: 2016-08-24
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
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41768219
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
 

Author Comment

by:W.E.B
ID: 41768579
Hi Neeraj,
I tried the code, but I'm not getting any results back.
please see sample attached.

thank you.
SampleUDF.xlsm
0
 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41768619
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
 

Author Closing Comment

by:W.E.B
ID: 41769582
Thank you very Much.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41769585
You're welcome. Glad to help.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question