VLOOKALL: VLOOKUP alternative that returns all, not just the first

If you use VLOOKUP often you occasionally come across a situation where a return of all hits would be helpful.  I found several attempts on the net – but most were too slow or had other shortfalls.  The following function allows you to use VLOOKALL just as you would VLOOKUP.  The only difference is the True/False piece of the equation.  Since VLOOKUP calls for a True/False at the end I added a fake variable so I wouldn’t have to change old habits…feel free to modify if you wish.


Public Function VLOOKALL(MatchWith As String, TRange As Range, returncol As Double, fakeFALSE) As String
    For i = 1 To TRange.Rows.Count
        If TRange(i, 1) = MatchWith Then
            If VLOOKALL <> "" Then
                VLOOKALL = VLOOKALL & ","
            End If
            VLOOKALL = VLOOKALL & TRange(i, returncol)
        End If
    Next i
End Function

One response

5 03 2015

Please help me modify this code, I want to be able to pick unique values when using VLOOKALL as my list contains duplicates in certain columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: