Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Is it possible to get multiple results in vlookup

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 17-02-2012
Member
 
Join Date: Oct 2011
Posts: 86
Is it possible to get multiple results in vlookup

I have a list of contract numbers (column A) and associated account numbers (column B). Some contract numbers are listed more than once because there may be multiple associated account numbers. My goal is to have one row/record for each contract number, with all the associated account numbers in a single cell, comma delimited. Can Excel do this.

Reply With Quote
  #2  
Old 17-02-2012
Member
 
Join Date: Jul 2011
Posts: 631
Re: Is it possible to get multiple results in vlookup

You can do the same with the help of a macro. With a macro something like for each c in selection if c=yournumber then mylist=mylist&","&c next c msgbox mylist. I had not properly written the code but you can understand the same by editing it properly.
Reply With Quote
  #3  
Old 17-02-2012
Member
 
Join Date: Jul 2011
Posts: 632
Re: Is it possible to get multiple results in vlookup

One more option: How about a UserDefinedFunction. 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.

Code:
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
Dim ubound_myRes 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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Is it possible to get multiple results in vlookup"
Thread Thread Starter Forum Replies Last Post
How to get multiple values of Vlookup in Single Cell Henriksen MS Office Support 4 25-02-2012 11:02 AM
Getting multiple matches result on Vlookup sum TheCarter MS Office Support 2 17-02-2012 02:08 PM
Excel - Multiple vlookup mike_2011 Windows Software 1 28-07-2011 04:40 AM
Multiple Matches Return To VLOOKUP Brunon Windows Software 5 26-11-2010 11:32 PM
Excel : vlookup multiple values Laler Windows Software 3 25-06-2009 11:27 AM


All times are GMT +5.5. The time now is 05:51 AM.