Vlookup with multiple records for one lookup value

example-multiple-records-vlookup.xlsx Hello. I have a worksheet in which I will have id's populated in rows and I need to vlookup another spreadsheet and retrieve records based on those ids. However, I need to retrieve all the records that appear each time for each id. For example, id XXX in the second spreadsheet has multiple rows populated, so i need to get all those rows to my worksheet 1. basically i need to vlookup multiple records for the same id. How can I do it, please? I really appreciate the help in advance.

Thank you,

Isabella
isabella duarteFinancial AnalystAsked:
Who is Participating?
 
Roy CoxConnect With a Mentor Group Finance ManagerCommented:
VLOOKUP will only return the first instance found.

Are you saying that the data is on one sheet within the same workbook? I would suggest AdvancedFilter with a macro to pull the data to another sheet.

In the example select an ID in the report sheet where indicated and press the button
AdvancedFilter.xlsm
2
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please upload a sample workbook.
1
 
Ejgil HedegaardCommented:
If the data are sorted on id, the first can be found with the Index/Match function, and the next by adding 1 to the Match, until the number of records with that id is reached.

If the data are not sorted on id, a helper column on the data sheet, can mark the records with ascending numbers for the id, and then an Index/Match function can search for the numbers.

If the data can not be sorted, and a helper column is not an option, it can be done with some VBA coding, and it can be triggered by the input, so it runs automatically.

A sample workbook with the data layout and the expected result layout will typically show what to do.
1
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Roy CoxGroup Finance ManagerCommented:
Surely adding 1 to each formula requires knowing the expected number of results, or multiple formulae many of which may not be used
1
 
Neil FlemingIndependent consultantCommented:
If the data you are searching are in a separate workbook, rather than just a different worksheet in the same workbook, you could also do this with an SQL connection to the workbook, and an SQL query.
1
 
Neil FlemingIndependent consultantCommented:
Attached is a sample SQL retrieval workbook.

Note that the connection string should work for Excel 2007 or later (certainly works for me in Excel 2016), and that the workbook requires the Microsoft ActiveX DataObjects library to be enabled in "tools|references".

The code retrieves records by ID from the "DataSource" worksheet and puts them in the "Main worksheet" based on which ID is selected. Results are placed in the sheet area below the named range "cRecs" (the cell which says "Records Found". Any previous contents of this area are deleted.

Code is below, in two procedures. GetData is called by the button in the attached sample. Currently as you will see it uses the activeworkbook as its SQL source, but you can equally set the workbook to a valid file name (with full path). The SQL query will pull from that file without opening it in Excel. NB the need for a "$" character on the end of the worksheet name in the query.

The second function opens the ADODB connection and runs the query.

Don't know if this helps with what you are trying to do, but there it is.

Option Explicit

Dim aC As ADODB.Connection
Dim rst As ADODB.Recordset

Sub getData()
Dim Q As String
Dim r As Range, rData As Range
Set r = Selection
    If r <> "" Then
    'create SQL query string
    Q = "SELECT ID,Name,Date,Details from [sourceData$] WHERE ID=" & r
    'call OpenExcelRST function to run query
    Set rst = OpenExcelRST(Q, ActiveWorkbook.FullName)
    'set output region:
    Set rData = [cRecs].CurrentRegion
    'clear previous data
    Set rData = rData.Offset(1, 0)
    rData.ClearContents
    'get results of query
    rData.CopyFromRecordset rst
    DoEvents
    'fit columns to data
    rData.EntireColumn.AutoFit
    End If
'dispose of ADODB connection and recordset:
Set aC = Nothing
Set rst = Nothing
End Sub


Function OpenExcelRST(sSql As String, sBook As String) As Recordset
'open adodb connection to Excel workbook
Set aC = New ADODB.Connection
Set OpenExcelRST = New ADODB.Recordset

'open connection to specificed workbook
aC.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                  "Data Source=" & sBook & ";" & _
                  "Extended Properties=Excel 8.0;" & _
                  "Persist Security Info=False"
                  
'run query:
OpenExcelRST.Open sSql, aC, adOpenStatic

ErrorExit:
    
Exit Function
     
'error handling:
errorHandler:
    Dim cnErrors As ADODB.Errors
    Dim ErrorItem As ADODB.Error
    Dim sError As String
     
    Set cnErrors = aC.Errors
     
    With Err
        sError = sError & vbCrLf & "VBA Error # : " & CStr(.Number)
        sError = sError & vbCrLf & "Generated by : " & .Source
        sError = sError & vbCrLf & "Description : " & .Description
    End With
     
    For Each ErrorItem In cnErrors
        With ErrorItem
            sError = sError & vbCrLf & "ADO error # : " & CStr(.Number)
            sError = sError & vbCrLf & "Description : " & .Description
            sError = sError & vbCrLf & "Source : " & .Source
            sError = sError & vbCrLf & "SQL State : " & .SqlState
        End With
    Next ErrorItem
    
    MsgBox sError, vbCritical, "SystemError"
    Resume ErrorExit
  
End Function

Open in new window

SQLdemo.xlsm
1
 
Ejgil HedegaardCommented:
With the setup you have I would not use a formula solution, but a Pivot table.
See sheet.

Some Used_ID are numbers, and to make them align in the pivot table, add a ' in front of the numbers, then it is text.
example-multiple-records-vlookup.xlsx
0
 
Roy CoxGroup Finance ManagerCommented:
I see the example, I'll add my code to it as soon as I can
0
 
Roy CoxGroup Finance ManagerCommented:
I've converted the data to a Table, this means the code will work with any additions to the Table. I've added rows above the data to use for entering criteria to filter by with AdvancedFilter. Clicking the button copies the required entries to the other sheet.
example-multiple-records-vlookup.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.