Solved

# Excel lookup - fomrula-macro

Posted on 2016-08-23
59 Views
Hello,

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 ....).

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 .

thanks,
Sample.xlsx
0
Question by:Wass_QA
• 3
• 2

LVL 28

Expert Comment

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
``````
Then use it like this......

In AB2
``````=GetVlookupData(\$M2,\$A\$2:\$G\$27,1)
``````

In AC2
``````=GetVlookupData(\$M2,\$A\$2:\$G\$27,6)
``````

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

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

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

thank you.
SampleUDF.xlsm
0

LVL 28

Accepted Solution

Subodh Tiwari (Neeraj) earned 500 total points
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
``````
0

Author Closing Comment

Thank you very Much.
0

LVL 28

Expert Comment

0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.