OK, So I got it figured out..... almost.
This is the formula I'm using: =LookUpConcat(A1,'Sheet 2!A$2:A$2250,'Sheet 2'!B$2:B$2250,", ")
I'm using it with this UDF:
Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
Dim X As Long, CellVal As String, ReturnVal As String, Result As String
If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
(ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
LookUpConcat = CVErr(xlErrRef)
Else
If Not MatchCase Then SearchString = UCase(SearchString)
For X = 1 To SearchRange.Count
If MatchCase Then
CellVal = SearchRange(X).Value
Else
CellVal = UCase(SearchRange(X).Value)
End If
ReturnVal = ReturnRange(X).Value
If MatchWhole And CellVal = SearchString Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
Result = Result & Delimiter & ReturnVal
End If
Continue:
Next
LookUpConcat = Mid(Result, Len(Delimiter) + 1)
End If
End Function
I got it to display correctly, when I take the wildcard ("?" in this case) out of my data in cell A1. But as you can see from my data below, I need the wildcard. My actual part #'s contain different letters that correspond with their attributes (S = small, M = Med, etc.). The red text is my desired result.
Any help on getting this to work with my wildcard?
Sheet 1
Row
 Column A
 Column B

1
 ABC123?.681
 =LookUpConcat(A1,'Sheet 2!A$2:A$2250,'Sheet 2'!B$2:B$2250,", ")

2
 ABC123?.290
 ABC123L.290

3
 ABC123?.540
 ABC123S.540

4
 ABC123?.999
 ABC123L.999,ABC123X.999

<tbody>
</tbody>
Sheet 2
Row
 Column A
 Column B

1
 ABC123S.681
 ABC123S.681

2
 ABC123M.681
 ABC123M.681

3
 ABC123L.290
 ABC123L.290

4
 ABC123S.540
 ABC123S.540

5
 ABC123L.999
 ABC123L.999

6
 ABC123X.999
 ABC123X.999

<tbody>
</tbody>