Pick up multiple results as text join and dynamic array range

[fornight2024]

Hi, I have uploaded here a sample.

I need to put in a formula similar to vlookup based on account but it needs to show all results. vlookup only shows the first results that appear.

I searched and found an answer with a formula that I do not really understand. The formula that I found was:

IFERROR(TEXTJOIN(", ",,FILTER(INDEX("namedrange",,2),INDEX("namedrange",,1)=OFFSET("cell",0,-2))),"")

I don't quite understand the last bit "Offset(cell,0,-2) and unable to apply to my case here. In addition, I don't see any "lookup" based on the account as reference.

 

[alansidman]

Here is a UDF from several years ago

Function MLOOKUP(ByRef TableArray As Range, ByVal LookupVal, ByRef LookupRange As Range, _
                                                        Optional ByVal NthMatch As Long)

' Author        : Krishnakumar @ ExcelFox.com


If Not TypeOf TableArray Is Range Then
    MLOOKUP = CVErr(2042)
    Exit Function
End If
If Not TypeOf LookupRange Is Range Then
    MLOOKUP = CVErr(2042)
    Exit Function
End If
If TableArray.Rows.Count <> LookupRange.Rows.Count Then
    MLOOKUP = CVErr(2042)
    Exit Function
End If
If TableArray.Columns.Count <> LookupRange.Columns.Count Then
    MLOOKUP = CVErr(2042)
    Exit Function
End If

Dim LV_Cnt      As Long 'Count Loookup Value
Dim KA1, KA2
Dim r As Long, c As Long
Dim fFoundNo    As Long
Dim n           As Long
Dim strLval     As String

If IsNumeric(LookupVal) Then
    LV_Cnt = Evaluate("countif(" & LookupRange.Address & "," & LookupVal & ")")
    fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address & ",0)")
ElseIf IsDate(LookupVal) Then
    LV_Cnt = Evaluate("countif(" & LookupRange.Address & "," & CLng(LookupVal) & ")")
    fFoundNo = Evaluate("match(" & CLng(LookupVal) & "," & LookupRange.Address & ",0)")
Else
    strLval = """" & LookupVal & """"
    LV_Cnt = Evaluate("countif(" & LookupRange.Address & "," & strLval & ")")
    fFoundNo = Evaluate("match(" & strLval & "," & LookupRange.Address & ",0)")
End If

If NthMatch > 0 Then
    If LV_Cnt = 0 Or NthMatch > LV_Cnt Then
        MLOOKUP = CVErr(2042)
        Exit Function
    End If
End If


KA1 = TableArray: KA2 = LookupRange

For r = fFoundNo To UBound(KA1, 1)
    For c = 1 To UBound(KA1, 2)
        If LCase$(KA2(r, c)) = LCase$(LookupVal) Then
            If NthMatch Then
                n = n + 1
                If n = NthMatch Then
                    MLOOKUP = KA1(r, c)
                    Exit Function
                End If
            Else
                MLOOKUP = MLOOKUP & "," & KA1(r, c)
            End If
        End If
    Next
Next
MLOOKUP = Mid$(MLOOKUP, 2)
End Function

Syntax

MLOOKUP(TableArray,LookupValue,LookupRange,[NthMatch])

If the optional NthMatch is missing, All the values will be concatenated in a single cell

 

[hagia_sofia]

Hello, if you have access to GROUPBY function you could try:

=CHOOSECOLS(GROUPBY(G2:G7,H2:H7,ARRAYTOTEXT,,0),2)

 

[Peter_SSs]

Is this what you are after?

25 01 07.xlsm
	B		D	E	F	G	H
1	Account		Reference			Account	Text
2	10100		Note 1, Note 2, Note 6			10100	Note 1
3	10200		Note 3			10100	Note 2
4	10300		Note 4			10200	Note 3
5	14500		Note 5			10300	Note 4
6						14500	Note 5
7						10100	Note 6
All results

Cell Formulas
Range	Formula
D2:D5	D2	=TEXTJOIN(", ",,FILTER(H$2:H$7,G$2:G$7=B2,""))

 

[fornight2024]

Is this what you are after?

25 01 07.xlsm
	B		D	E	F	G	H
1	Account		Reference			Account	Text
2	10100		Note 1, Note 2, Note 6			10100	Note 1
3	10200		Note 3			10100	Note 2
4	10300		Note 4			10200	Note 3
5	14500		Note 5			10300	Note 4
6						14500	Note 5
7						10100	Note 6
All results

Cell Formulas
Range	Formula
D2:D5	D2	=TEXTJOIN(", ",,FILTER(H$2:H$7,G$2:G$7=B2,""))

Thank you!!!!!

This is so much easier to understand than the answer I found in my original post!

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.

You may prefer the above simpler formula - which needs to be copied down ..
.. or you could use this slightly more complex one (similar logic in it though) - which does not need to be copied down at all.

25 01 07.xlsm
	B		D	E	F	G	H
1	Account		Reference			Account	Text
2	10100		Note 1, Note 2, Note 6			10100	Note 1
3	10200		Note 3			10100	Note 2
4	10300		Note 4			10200	Note 3
5	14500		Note 5			10300	Note 4
6						14500	Note 5
7						10100	Note 6
All results (2)

Cell Formulas
Range	Formula
D2:D5	D2	=BYROW(B2:B5,LAMBDA(r,TEXTJOIN(", ",,FILTER(H2:H7,G2:G7=r))))
Dynamic array formulas.