How to get multiple column values in vlookup using VBA?

Question

I have a excel file which looks like below:

Now, I've created a list in cell "L2" using the values in the name column and added a button "Find".

What I'm trying to do is when I click the button after selecting a value in "L2", it will give me the respective values of Name, ID, DOB, Email from the table "A4:E12" in the cells "I6:L6" and if "I6:L6" has values then it will move to "I7:L7" and so on every time I hit the button.

Here is the code I've done:

Sub getValues()
Range("I6").Select
ActiveCell.Formula = "=VLOOKUP($L$2,$A$5:$E$12,{1,2,4,5},0)"
Range("I6").AutoFill Destination:=Range("I6:L6"), Type:=xlFillDefault
Range("I6:L6").Select
Range("A3").Select
End Sub

The problem with this code is it is giving the values like below:

Where expected result is:

Also, it should automatically paste the values to "I7:L7" if "I6:L6" is occupied and so on.

Can anyone show me how can I do that?

Tamal Banerjee · Accepted Answer · 2019-11-09 14:36:32Z

This is a long answer (and not the most efficient) but I wrote it this way to illustrate it step by step

See comments inside the code and adapt it to fit your needs

Public Sub GetDataByPersonName()

    Dim sourceSheet As Worksheet
    Dim sourceDataRange As Range

    Dim rowOffset As Long

    Dim sourceSheetName As String
    Dim sourcePersonName As String
    Dim sourceRangeAddress As String
    Dim sourceLookupTableRange As String
    Dim sourceMatchedRow As Variant

    ' Results
    Dim resultsInitialCellAddress As String
    Dim resultName As Variant
    Dim resultID As Variant
    Dim resultCountry As Variant
    Dim resultDOB As Variant
    Dim resultEmail As Variant

    ' Adjust to your needs
    sourceSheetName = "Sheet1" ' Sheet name where data is located
    sourceRangeAddress = "L2" ' Cell address of enter name
    sourceLookupTableRange = "A4:E12" ' Range address where lookup data is located
    resultsInitialCellAddress = "I4" ' Cell address of headers destination where results are going to be added

    ' Set references to sheet and lookup range
    Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
    Set sourceDataRange = sourceSheet.Range(sourceLookupTableRange)

    ' Get the current name to be looked up
    sourcePersonName = sourceSheet.Range(sourceRangeAddress).Value

    ' Lookup the results (see columns(1) to check the column where name is located
    sourceMatchedRow = Application.Match(sourcePersonName, sourceDataRange.Columns(1), 0)

    If Not IsError(sourceMatchedRow) Then
        ' Find the last empty row below results header
        If sourceSheet.Range(resultsInitialCellAddress).Offset(1, 0).Value = vbNullString Then
            rowOffset = 1
        Else
            rowOffset = sourceSheet.Range(resultsInitialCellAddress).End(xlDown).Row - sourceSheet.Range(resultsInitialCellAddress).Row + 1
        End If

        ' Name (see name column is 1 at the end for line)
        resultName = Application.WorksheetFunction.Index(sourceDataRange, sourceMatchedRow, 1)
        resultID = Application.WorksheetFunction.Index(sourceDataRange, sourceMatchedRow, 2)
        resultCountry = Application.WorksheetFunction.Index(sourceDataRange, sourceMatchedRow, 3)
        resultDOB = Application.WorksheetFunction.Index(sourceDataRange, sourceMatchedRow, 4)
        resultEmail = Application.WorksheetFunction.Index(sourceDataRange, sourceMatchedRow, 5)

        ' Dump results into worksheet
        sourceSheet.Range(resultsInitialCellAddress).Offset(rowOffset, 0).Value = resultName
        sourceSheet.Range(resultsInitialCellAddress).Offset(rowOffset, 1).Value = resultID
        sourceSheet.Range(resultsInitialCellAddress).Offset(rowOffset, 2).Value = resultCountry
        sourceSheet.Range(resultsInitialCellAddress).Offset(rowOffset, 3).Value = resultDOB
        sourceSheet.Range(resultsInitialCellAddress).Offset(rowOffset, 4).Value = resultEmail

    End If

End Sub

Remeber to mark the answer if it helped you, to help others

ASH · Accepted Answer · 2019-11-10 00:24:22Z

0

Ok, I think you should try to do this yourself, and record your steps in a Macro. You will learn a ton that way. See the link below.

https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/

Turn on the Macro recorder before you click though the steps. Then, you will have all the code you need!!

answered Nov 10, 2019 at 0:24

ASH

20.5k28 gold badges116 silver badges247 bronze badges

Collectives™ on Stack Overflow

How to get multiple column values in vlookup using VBA?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related