Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Tanya
 
Posts: n/a
Default vlookup multiple text rows

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.
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
TommySzalapski
 
Posts: n/a
Default


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

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Tanya
 
Posts: n/a
Default

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I use vlookup for multiple occurrences of the same value Edith F Excel Worksheet Functions 15 April 29th 05 06:12 PM
how do I use vlookup for multiple occurrences of the same value bj Excel Worksheet Functions 0 April 27th 05 10:43 PM
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM
Convert multiple columns to rows Lois Lane Excel Worksheet Functions 8 January 10th 05 01:47 AM
Multiple rows of data on a single axis (charting) ramseysgirl Charts and Charting in Excel 8 December 29th 04 07:00 PM


All times are GMT +1. The time now is 12:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"