Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Vlookup Multiple Values By Adding Formula With Loop In VBA

  1. #1

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    So what is it that you want? Do you want to loop through a column and return the values from an adjacent column, and give the values from there when the value in the first column matches the value you are looking up?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    So what is it that you want? Do you want to loop through a column and return the values from an adjacent column, and give the values from there when the value in the first column matches the value you are looking up?
    You are right I want it to return as normal vlookup do. Like if the lookup value is repeated in a column, it want it to return every result.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Can be done. Can you post a sample file and show the expected output also.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0
    I have attatched the file. Can you please write a vba and formula, if any, for the task?

    Regards,
    Safal
    Attached Files Attached Files

  6. #6
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Try this formula:

    Copy below formula and apply it with CSE and drag it down:

    =IFERROR(INDEX(thingstodo,SMALL(IFERROR(IF((things todo[Date]=$D$11),ROW($A$1:$A$100),""),""),ROW($A1)),2),"")

  7. #7
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0
    I can't drag it down. I need it to be automatical. So I was asking for the loop.

    Regards,
    Safal

  8. #8
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Then it can be done with the help of VBA Macro. Is it Ok?

  9. #9
    Member
    Join Date
    Jun 2012
    Posts
    39
    Rep Power
    0
    Ok. Thanks

    Regards,
    Safal

  10. #10
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    13
    Try this:

    Code:
    Sub LMP_Test()
    
        Dim rngData                     As Range
        Dim rngFirstValue               As Range
        Dim rngValue                    As Range
        Dim varResult()                 As Variant
        Dim strFindWhat                 As String
        Dim lngCount                    As Long
        
        Const strShtName                As String = "calcs"
        Const strDataStartCell          As String = "C4"
        Const strCriteriaCell           As String = "D11"
        Const strResultCell             As String = "C15"
        Const strBlankArrayVal          As String = "ArrayIsBlankWithNoDataFound"
        
        With Worksheets(strShtName)
            Set rngData = .Range(strDataStartCell).CurrentRegion
            strFindWhat = .Range(strCriteriaCell).Value
            With rngData.Resize(, 1)
                Set rngValue = .Find(strFindWhat, LookIn:=xlValues)
                If Not rngValue Is Nothing Then
                    Set rngFirstValue = rngValue
                    Set rngValue = Nothing
                    lngCount = 1
                    ReDim varResult(1 To lngCount)
                    varResult(LBound(varResult)) = strBlankArrayVal
                    Do
                        If rngValue Is Nothing Then
                            Set rngValue = .FindNext(rngFirstValue)
                            varResult(lngCount) = rngValue.Offset(, 1).Value
                        Else
                            lngCount = lngCount + 1
                            Set rngValue = .FindNext(rngValue)
                            ReDim Preserve varResult(1 To lngCount)
                            varResult(lngCount) = rngValue.Offset(, 1).Value
                        End If
                    Loop While Not rngValue Is Nothing And rngValue.Address <> rngFirstValue.Address
                End If
            End With
            If varResult(LBound(varResult)) <> strBlankArrayVal Then
                .Range(strResultCell).Resize(10000).ClearContents
                varResult = Application.Transpose(varResult)
                .Range(strResultCell).Resize(UBound(varResult), 1).Value = varResult
            End If
        End With
        
        Set rngData = Nothing
        Set rngFirstValue = Nothing
        Set rngValue = Nothing
        Erase varResult
        strFindWhat = vbNullString
        lngCount = Empty
    
    End Sub

Similar Threads

  1. Vlookup to Return Multiple Values
    By Admin in forum Download Center
    Replies: 9
    Last Post: 02-17-2017, 07:03 PM
  2. Loop to two columns and Concatenate values
    By ivandgreat in forum Excel Help
    Replies: 15
    Last Post: 04-14-2013, 08:20 PM
  3. Loop Through And Delete Multiple File Types In A Folder
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 03-30-2013, 04:47 PM
  4. Adding values less than zero
    By Howardc in forum Excel Help
    Replies: 3
    Last Post: 07-14-2012, 11:55 AM
  5. Loop and Check For Values In Entire Column in Excel
    By Jeff5019 in forum Excel Help
    Replies: 3
    Last Post: 05-01-2012, 10:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •