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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please upload a sample workbook.
1
Roy CoxGroup 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 FlemingConsultant and developerCommented:
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 FlemingConsultant and developerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.