excel vlookup with multiple results

by

Here is a function that will do it for you. It’s a little different from Vlookup in that you will only give it the search column, not the whole range, then as the third parameter you will tell it how many columns to go left (negative numbers) or right (positive) in order to get your return value.

I also added the option to use a seperator, in your case you will use ” “. Here is the function call for you, assuming the first row with Acct No. is A and the results is row B:

=vlookupall("0001", A:A, 1, " ")

Here is the function:

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional seperator As String = ", ") As String

Dim i As Long
Dim result As String

For i = 1 To lookup_column.Rows.count
    If Len(lookup_column(i, 1).text) <> 0 Then
        If lookup_column(i, 1).text = lookup_value Then
            result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
        End If
    End If
Next

If Len(result) <> 0 Then
    result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

Notes:

  • I made “, ” the default seperator for results if you don’t enter one.
  • If there is one or more hits, I added some checking at the end to
    make sure the string doesn’t end with an extra seperator.
  • I’ve used A:A as the range since I don’t know your range, but
    obviously it’s faster if you enter the actual range.

More Related Contents:

  1. How to convert to percentage [closed]
  2. Excel Number Separation [closed]
  3. Sequence of numbers based on start and end value of 2 cells [closed]
  4. I have an Excel data sheet that I would like to extract data from into a new sheet
  5. Error in finding last used cell in Excel with VBA
  6. Getting unique values in Excel by using formulas only
  7. Vlookup using 2 columns to reference another
  8. Excel: last character/string match in a string
  9. Referencing value in a closed Excel workbook using INDIRECT?
  10. Simple Pivot Table to Count Unique Values
  11. Excel UNIQUE Across Columns
  12. Ignore Duplicates and Create New List of Unique Values in Excel
  13. Filling any empty cells with the value above
  14. Find last used cell in Excel VBA
  15. Remove leading or trailing spaces in an entire column of data
  16. Two column lookup in table array using INDEX and MATCH
  17. Excel Date to String conversion
  18. If two cells match, return value from third
  19. Excel: Searching for multiple terms in a cell
  20. What does — do in Excel formulas?
  21. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  22. Countif With Multiple OR Criteria
  23. Dynamic chart range using INDIRECT: That function is not valid (despite range highlighted)
  24. CountIf With Filtered Data
  25. Convert from US to UK format
  26. Extract the last substring from a cell
  27. Date-Formatting in TEXT formula independent from used language in Excel
  28. Check whether a cell contains a substring
  29. Unique Rank value for a subgroup within a group
  30. Get content of a cell given the row and column numbers

Leave a Comment