Excel formula to return multiple column names

Question

I am trying to get a formula that searches each row for a "Yes". It then should list the column name(s). Some row may only have 1 yes, some may have 3 or more. I have tried searching and editing several suggestions, but cannot get the one I need. Some formulas will give me a random column name, and only 1. Other just error out.

Header 1    Header 2    Header 3    Header 4    List
  No          Yes        Yes           No       Header 2, Header 3
 Yes           No        Yes          Yes       Header 1, Header 3, Header 4
  No           No         No          Yes       Header 4
 Yes          Yes         No          Yes       Header 1, Header 2, Header 4

If you do not have Office 365 Excel then see: http://stackoverflow.com/questions/36572697/udf-to-concatenate-values If you do then look into TEXTJOIN(). — Scott Craner, Aug 31 '16 at 20:12
it is 2013, the first one you linked is exactly what end result I want, was just hoping to not use coding as I am even worse at that lol, I will try that though now — Adrienne, Aug 31 '16 at 20:20

Scott Craner · Accepted Answer · 2019-09-17T19:17:02.823

You can use the following UDF:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Put it in a module attached to the worksheet.

Then you would call it like any other formula with the following array formula:

=TEXTJOIN(",",TRUE,IF(A2:D2="Yes",$A$1:$D$1,""))

Being an array it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

To get it with IF formulas this will return the same thing, since you only have four. If you have more than four this would get quite long.

=LEFT(IF(A2="Yes",$A$1 & ",","") & IF(B2="Yes",$B$1 & ",","") & IF(C2="Yes",$C$1 & ",","") & IF(D2="Yes",$D$1 & ",",""),LEN(IF(A2="Yes",$A$1 & ",","") & IF(B2="Yes",$B$1 & ",","") & IF(C2="Yes",$C$1 & ",","") & IF(D2="Yes",$D$1 & ",",""))-1)

That UDF you just gave works great, exactly what I needed. Thank you! — Adrienne, Aug 31 '16 at 20:45
@scott was just trying to use your UDF one a basic row with nothing special, just numbers. All I was trying to do was combine the cells of a row and put a comma in between the values. I keep getting the result of #Value! The UDF from this question is the only code in the worksheet. Does the worksheet need to be saved first? `=textjoin(",",TRUE,A24:J24)` is what I have for a formula currently. — Forward Ed, Sep 17 '19 at 19:08
use this one instead: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell @ForwardEd — Scott Craner, Sep 17 '19 at 19:15
Thank you. I will take a look at this. I stumbled on this solution and a few other UDFs. I was originally looking the TEXTJOINT array fomula alternative that I thought you had for 2013 users, but my google skills failed me. — Forward Ed, Sep 17 '19 at 19:25
@ForwardEd I do have one that is a TEXTJOINIFS(): https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a — Scott Craner, Sep 17 '19 at 19:36

score 0 · Answer 2 · answered Aug 31 '16 at 20:28

I think you can try something like that:

    =(IF(EXACT(A2;"Yes");A1 & ", ";"")) & (IF(EXACT(B2;"Yes");B1 & ", ";""))  & (IF(EXACT(C2;"Yes");C1 & ", ";"")) & (IF(EXACT(D2;"Yes");D1 & ", ";""))

It will test if the cell string correspond to "Yes", if it is the case, the string written is the header, otherwise the string written is "". I have also concatenated with three others IF condition with & symbol.

Applying this formula on the first line of your array return the following result:

    header 2, header 3,

I thing you can improve it to get exactly what you want.

sancho.s ReinstateMonicaCellio · Answer 3 · 2016-08-31T20:47:13.070

There is no need for an UDF

Use

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A2="Yes",A$1&", ","")&IF(B2="Yes",B$1&", ","")&IF(C2="Yes",C$1&", ","")&IF(D2="Yes",D$1&", ",""),", H","-H"),", ",""),"-H",", H")

SUBSTITUTEs are a (possibly overkill) way of removing the trailing ", ", only if needed. Copy down in as many rows as needed.

I meant to write an array formula, which would work for any number of columns without explicitly writing each one, but apparently CONCATENATE does not work with array formulas.

Excel formula to return multiple column names

3 Answers3

Linked

Related