CODEWITHSUNDEEP
excelvba
Tamal Banerjee
Tamal Banerjee

Reputation: 503

How to get multiple column values in vlookup using VBA?

I have a excel file which looks like below: sample file

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: result

Where expected result is:

expected

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?

Upvotes: 0

Views: 407

Answers (2)

ASH
ASH

Reputation: 20342

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!!

Upvotes: 0

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

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

Upvotes: 1

Related Questions