vlookup with multiple text value return

[Pizzio]

I'm trying to create a formula that returns with all the text values for all the conditions. So similar to a sumproduct where the formula returns a summed value of ALL the conditions i need the same but for text. Any ideas???

hello no
hello no
hello yes
goodbye no

formula would return with (no, no, yes).

 

[Pizzio]

Is there another formula that can handle up to 500 rows of data? When i used {=substitue(TRIM(MCONCAT(TRANSPOSE(IF(Reference!$B$2:$B$250=C9,Reference!$V$2:$V$250,"")),"")),"","")} it #VALUE'd after about 64 rows.

 

[Aladin Akyurek]

Is there another formula that can handle up to 500 rows of data? When i used {=substitue(TRIM(MCONCAT(TRANSPOSE(IF(Reference!$B$2:$B$250=C9,Reference!$V$2:$V$250,"")),"")),"","")} it #VALUE'd after about 64 rows.

lengthy return strings pre-empts the limits that hold for MCONCAT. I'm not sure whether ACONCAT (code by Grove, available in some of the threads here) would do better. It's worth trying though.

 

[Pizzio]

Could you give me an example?

 

[Aladin Akyurek]

Could you give me an example?

Why do you ask for an example? I was just suggesting that you replace MCONCAT with ACONCAT in order to see whether that fars better...

 

[Pizzio]

I tried that and it failed on my end. I was hoping for an example so that I could see what i was doing wrong.

 

[Pizzio]

I've tried using the below formula but I'm still getting a #NAME? reference. I've confirmed that the referece $B$2:$B$250 have no oddities and match what is refelected in cell R250 and $V$2:$V$250 contains a concat formula that pulls together my notes. Any ideas on what i'm doing wrong? Do i have to many rows? is there another formula i should be using that will bring together all by notes?

=SUBSTITUTE(TRIM(ACONCAT(TRANSPOSE(IF(Reference!$B$2:$B$250=R250,Reference!$V$2:$V$250," "))," "))," ",",")

 

[Aladin Akyurek]

I've tried using the below formula but I'm still getting a #NAME? reference. I've confirmed that the referece $B$2:$B$250 have no oddities and match what is refelected in cell R250 and $V$2:$V$250 contains a concat formula that pulls together my notes. Any ideas on what i'm doing wrong? Do i have to many rows? is there another formula i should be using that will bring together all by notes?

=SUBSTITUTE(TRIM(ACONCAT(TRANSPOSE(IF(Reference!$B$2:$B$250=R250,Reference!$V$2:$V$250," "))," "))," ",",")

Did you add ACONCAT to your workbook? the #NAME? error would suggest that you probably did not...

 

[Pizzio]

i did with a control ALT enter and it's coming back with NAME?. I'm not sure what i'm doing wrong here. Even when dropping the range to 2-25 it's coming back the #NAME? I'm not sure the formula is going to work in this string. Thoughts?

{=SUBSTITUTE(TRIM(ACONCAT(TRANSPOSE(IF(Reference!$B$2:$B$25=R25,Reference!$V$2:$V$250," "))," "))," ",",")}

 

[Aladin Akyurek]

i did with a control ALT enter and it's coming back with NAME?. I'm not sure what i'm doing wrong here. Even when dropping the range to 2-25 it's coming back the #NAME? I'm not sure the formula is going to work in this string. Thoughts?

{=SUBSTITUTE(TRIM(ACONCAT(TRANSPOSE(IF(Reference!$B$2:$B$25=R25,Reference!$V$2:$V$250," "))," "))," ",",")}

Are Trim, Transpose, and all those spaces really needed?...

=SUBSTITUTE(MCONCAT(IF(Reference!$B$2:$B$250=R25,","&Reference!$V$2:$V$250,"")),",","",1)

and its equivalent with ACONCAT would be:

=SUBSTITUTE(ACONCAT(IF(Reference!$B$2:$B$250=R25,","&Reference!$V$2:$V$250,"")),",","",1)

BTW, the code for ACONCAT is:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            aconcat = aconcat & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            aconcat = aconcat & y & sep
        Next y
    Else
        aconcat = aconcat & a & sep
    End If
    aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[mikerickson]

If you want to go the UDF route, the first three arguments for ConcatIf mirror the arguments of SUMIF, the optional Delimiter and NoDuplicates arguments are self explainitory.
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=49><b>A</b><td align=center width=50><b>B</b><td align=center width=45><b>C</b><td align=center width=114><b>D</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Hello</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">alpha</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">alpha,beta,epsilon</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">hello</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">beta</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">goodby</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">gamma</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000">goodby</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">delta</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">hello</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">epsilon</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>D1 <td align = left >=ConcatIf(A:A,"hello",B:B,",")</tr></table>

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