Vlookup

[Solved/Closed]
Report
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello guys,

I wonder if you could advise: when I set my regular vlookup. And have found the requested result. How do I get excel to keep searching for further results that fit my condition. For example. I want to have all the Peter listed and not only the first one the function finds from a list.
Any suggestions please?


2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
with vlookup that would not be possible. You would need to create a custom formula to to that
Posts
38
Registration date
Monday January 2, 2012
Status
Member
Last seen
April 21, 2018

Any idea what would do the job? I realise that I would need another cell to insert another formula that would exclude the already displayed result from the vlookup. So let's say I have 3 peters in the list and the first one is found, what could be the solution to have the remaining two displayed under the first one.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You can use search to do that

here is some thing that can do that
Sample call
=FindAll("a",Sheet3!A:B,2)


Public Function FindAll(searchFor As String, rngSearchAndFindRange As Range, offsetCol As Integer) As String
    
    Dim Cell                    As Range
    Dim firstFoundCell          As Range
    Dim returnValue             As String
    Dim searchRange             As Range
     
    If (rngSearchAndFindRange Is Nothing) Then
        FindAll = vbNullString
        
    ElseIf (rngSearchAndFindRange.Columns.Count < offsetCol) Then
          FindAll = vbNullString
     
    Else
      With rngSearchAndFindRange
          Set searchRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1))
      End With
      
      With searchRange
    
            Set Cell = .Find(What:=searchFor, _
                            After:=.Cells(.Rows.Count, .Columns.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
                            SearchFormat:=False _
                            )
                                    
            If Not Cell Is Nothing Then
                Set firstFoundCell = Cell
                returnValue = Cell.Offset(0, offsetCol - 1).Value
    
                Do While True
                
                    Set Cell = .Find(What:=searchFor, _
                            After:=.Range(Cell.Address), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False, _
                            SearchFormat:=False _
                            )
            
                    If Not Cell Is Nothing Then
                        If firstFoundCell.Address = Cell.Address Then Exit Do
                        returnValue = returnValue & ", " & Cell.Offset(0, offsetCol - 1).Value
                    Else
                        Exit Do
                    End If
                Loop
                
                FindAll = returnValue
    
            Else
               FindAll = vbNullString
       
            End If
            
        End With
    End If

    Set Cell = Nothing
    Set firstFoundCell = Nothing
    Set searchRange = Nothing

End Function