vlookup multiple text rows

[Tanya]

I am using the vlookup function to auto populate a template. The area that i
have a problem with is i have multiple rows of text that belong to the same
lookup reference. I need all those rows of text to populate the field and
currently it is only taking the last line. I am not sure if I can do some
sort of formula to concatenate those rows based on the lookup value.

[Dave Peterson]

Saved from a previous post:

How about a UserDefined Function?

Option Explicit
Function mvlookup2(lookupValue, tableArray As Range, colIndexNum As Long, _
Optional NotUsed As Variant) As Variant

Dim initTable As Range
Dim myRowMatch As Variant
Dim myRes() As Variant
Dim myStr As String
Dim initTableCols As Long
Dim i As Long

Set initTable = Nothing
On Error Resume Next
Set initTable = Intersect(tableArray, _
tableArray.Parent.UsedRange.EntireRow)
On Error GoTo 0

If initTable Is Nothing Then
mvlookup2 = CVErr(xlErrRef)
Exit Function
End If

initTableCols = initTable.Columns.Count

i = 0
Do
myRowMatch = Application.Match(lookupValue, initTable.Columns(1), 0)

If IsError(myRowMatch) Then
Exit Do
Else
i = i + 1
ReDim Preserve myRes(1 To i)
myRes(i) _
= initTable(1).Offset(myRowMatch - 1, colIndexNum - 1).Text
If initTable.Rows.Count <= myRowMatch Then
Exit Do
End If
On Error Resume Next
Set initTable = initTable.Offset(myRowMatch, 0) _
.Resize(initTable.Rows.Count - myRowMatch, _
initTableCols)
On Error GoTo 0
If initTable Is Nothing Then
Exit Do
End If
End If
Loop

If i = 0 Then
mvlookup2 = CVErr(xlErrNA)
Exit Function
End If

myStr = ""
For i = LBound(myRes) To UBound(myRes)
myStr = myStr & ", " & myRes(i)
Next i

mvlookup2 = Mid(myStr, 3)

End Function

It uses the almost the same syntax as the =vlookup() function. But it always
uses "false" as the 4th argument--no matter what you type.

Select a range (single column/single row) with enough cells to fill in your data
(any cells not used will appear empty).

Then type in your formula:

=mvlookup2(a1,sheet2!$a$1:$c$999,3,false)
(mvlookup2 = multiple Vlookup)
(2 because this one is different from my original. You can change it (all
spots) if you want to.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tanya wrote:
>
> I am using the vlookup function to auto populate a template. The area that i
> have a problem with is i have multiple rows of text that belong to the same
> lookup reference. I need all those rows of text to populate the field and
> currently it is only taking the last line. I am not sure if I can do some
> sort of formula to concatenate those rows based on the lookup value.

--

Dave Peterson

[TommySzalapski]

If that confuses you, you could just concatenate multiple vlookups
Concatenate(vlookup(A1, B1:D7, 2, false), vlookup(A1, B1:D7, 3, false)) and so on, but Dave's solution is cleaner. It's just pretty complex if you've never used UDFs.

Szalapski

[Dave Peterson]

Actually, you're returning the values from the same row (single unique key to
match). That UDF returns values from different rows when the key is duplicated
in the table.

TommySzalapski wrote:
>
> If that confuses you, you could just concatenate multiple vlookups
> Concatenate(vlookup(A1, B1:D7, 2, false), vlookup(A1, B1:D7, 3, false))
> and so on, but Dave's solution is cleaner. It's just pretty complex if
> you've never used UDFs.
>
> Szalapski
>
> --
> TommySzalapski
> ------------------------------------------------------------------------
> TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
> View this thread: http://www.excelforum.com/showthread...hreadid=395310

--

Dave Peterson

[Tanya]

Thanks you guys, I am new to UDF's but i'll give it a whirl!

"Dave Peterson" wrote:

> Actually, you're returning the values from the same row (single unique key to
> match). That UDF returns values from different rows when the key is duplicated
> in the table.
>
> TommySzalapski wrote:
> >
> > If that confuses you, you could just concatenate multiple vlookups
> > Concatenate(vlookup(A1, B1:D7, 2, false), vlookup(A1, B1:D7, 3, false))
> > and so on, but Dave's solution is cleaner. It's just pretty complex if
> > you've never used UDFs.
> >
> > Szalapski
> >
> > --
> > TommySzalapski
> > ------------------------------------------------------------------------
> > TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
> > View this thread: http://www.excelforum.com/showthread...hreadid=395310
>
> --
>
> Dave Peterson
>