Vlookup to return multiple answers

[cjdecarr]

Hello,

I have a sheet that allows the user to type in a description and then it returns the important information about the description from the second sheet in the work book. I need a way for multiple answers to be displayed if there are. Here is an example: We have 3 Bryan Smiths in the office and we are trying to find out what floor they work on and their hire date. I want the user to be able to type in the cell "Smith, Bryan" and then the formula will pull up the 3 Bryan Smith's located on sheet 2 and list them below the input cell. I am not sure if this can be done without a macro or VB as I am not very good with either of those and was hoping it could be done with a formula like VLOOKUP. Thanks for the assist.

 

[63falcondude]

Perhaps this can help you How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP

 

[mikerickson]

This UDF, ConcatIf, can act like a multiple VLOOKUP.

=ConcatIf(A:A, "Smith, Bryan", B:B, ", ")

will return all the data in column B that matches a column A entry of Smith, Bryan.

You will have to adjust the arguments to match your data layout.

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
    Dim i As Long, j As Long
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
    stringsRange.Column - compareRange.Column)
     
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function