textjoin if

In the below image you can see left table and right table. In the right table I want Students names in a single cell for each remark value by using left table as an input data. 

In another words one can say it is multiple values Vlookup in single cell or textjoin if.

So, let’s explore various way to do this.  

Using an array formula which is a combination of Textjoin and If function

Cell G3 formula =TEXTJOIN(“-“,TRUE,IF($B$3:$B$14=F3,$A$3:$A$14,””))

Since above formula is an array formula hence press CTRL+SHIFT+ENTER instead of ENTER after writing formula to make it array formula.

In the below image you can see entire formula is wrapped within curly {} brackets, which means it is an array formula.

Working of array formula

In nutshell the below formula is basically Textjoin function.
=TEXTJOIN(“-” , TRUE, IF($B$3:$B$14=F3,$A$3:$A$14,””) )

1st input (yellow) is delimiter or separator which is dash “-“. You can put any other delimiter also.

2nd input (green) is True  which means ignore blank present in join range or text array.

3rd input (pink) is if statement which gives array of texts based on evaluation. Select if statement and press F9 you will see something like the below

{“”;””;”Jatin”;””;”Mohan”;””;””;”Rakul”;””;””;””;””}

If statement in Cell G3 is  if remark equals “Fit” then output respective student names else nothing “”. So Jatin, Mohan, Rakul have Fit remark.

=TEXTJOIN(“-” , TRUE, {“”;””;”Jatin”;””;”Mohan”;””;””;”Rakul”;””;””;””;””} )

Since second input is True which means ignore blank or null string hence “” will be ignored and Names will get joined with a given separator.

Note:- Textjoin function is available in Excel version 2019 and above. If your Excel version is below 2019 then use UDF method. UDF method will work in any Excel version.

In if statement you can also use <>, >, >=, <, <=  comparison operators.

Array formula =TEXTJOIN(“-“,TRUE,IF($B$3:$B$14<>F3,$A$3:$A$14,””)) will give not fit student names. This will output the below

Fatima-Jade-Kshama-Prakash-Preeti-Sachin-Sohan-Sunny-Tripti

In the above image cell G3 array formula is 

=TEXTJOIN(“-“,TRUE,IF($B$3:$B$6>F3,$A$3:$A$6,””)) which outputs spell of number greater than 2 (Three-Four).

Textjoin function in Excel

Using UDF (User Defined Function) named textJoinIf_1

Press ALT+F11 to open VBA IDE . Add new standard module by clicking on Insert tab then click on module.

Double click on newly created standard module and copy paste the below code in that module. Make sure to save you workbook either in .xlsb format or .xlsm format. 

You can see UDF function name is textJoinIf_1.

Option Explicit
Option Compare Text
Function textJoinIf_1(joinRange As Range, Optional criRng As Range, _
 Optional cVal As String, Optional deli As String = ",") As String
   If criRng Is Nothing Then
       textJoinIf_1 = concatRngVal(joinRange, deli)
       Exit Function
   End If
  Dim valRngAr As Variant, criRngAr As Variant
  Dim val As Variant, str As String, i As Long, clmnCnt As Integer
  valRngAr = joinRange.Value
  clmnCnt = criRng.Columns.Count
  criRngAr = criRng.Value
  str = ""
  i = 1
  For Each val In criRngAr
        If val = cVal Then
          If clmnCnt = 1 Then
            str = str & valRngAr(i, 1) & deli
          Else
            str = str & valRngAr(1, i) & deli
          End If
        End If
   i = i + 1
  Next val
  If deli <> "" And str <> "" Then str = Left(str, Len(str) - 1)
  textJoinIf_1 = str
End Function
Private Function concatRngVal(joinRange As Range, Optional deli As String = ",") As String
 If joinRange.Columns.Count = 1 Then
    concatRngVal = _
    Join(Application.Transpose(joinRange.Value), deli)
 Else
    concatRngVal = _
    Join(Application.Transpose(Application.Transpose(joinRange.Value)), deli)
 End If
End Function

After doing above steps you are ready to use textJoinIf_1 UDF function.

Cell G4 formula =textJoinIf_1($A$3:$A$14, $B$3:$B$14, F4,”-“)

1st input is join range, 2nd is criteria range, 3rd is value to test, 4th is delimiter.

If you don not supply delimiter then default delimiter is comma “,”.

This UDF function only tests equality means = comparison operator.

Only 1st input is mandatory rest is optional.

If you supply only 1st input then it will just perform Textjoin and joins all the values of a range.

=textJoinIf_1($A$3:$A$14) outputs

Fatima,Jade,Jatin,Kshama,Mohan,Prakash,Preeti,Rakul,Sachin,Sohan,Sunny,Tripti

Using advance UDF named textJoinIf_2

Create new standard module as I explained for textJoinIf_1 UDF. 

Copy paste the below code in that module.

Option Explicit
Option Compare Text
Function textJoinIf_2(joinRange As Range, Optional criRng As Range, _
 Optional compOpr As String, Optional cVal As Variant, Optional deli As String = ",") As String
   If criRng Is Nothing Then
       textJoinIf_2 = concatRngVal(joinRange, deli)
       Exit Function
   End If
  Dim valRngAr As Variant, criRngAr As Variant
  Dim val As Variant, str As String, i As Long, clmnCnt As Integer
  valRngAr = joinRange.Value
  clmnCnt = criRng.Columns.Count
  criRngAr = criRng.Value
  str = ""
  i = 1
  For Each val In criRngAr
        If compareExpressionAns(compOpr, val, cVal) Then
          If clmnCnt = 1 Then
            str = str & valRngAr(i, 1) & deli
          Else
            str = str & valRngAr(1, i) & deli
          End If
        End If
   i = i + 1
  Next val
  If deli <> "" And str <> "" Then str = Left(str, Len(str) - 1)
  textJoinIf_2 = str
End Function
Private Function compareExpressionAns _
(compOperStr As String, val1 As Variant, val2 As Variant) As Boolean
  Select Case compOperStr
        Case "="
            compareExpressionAns = (val1 = val2)
        Case "<>"
            compareExpressionAns = (val1 <> val2)
        Case ">"
            compareExpressionAns = (val1 > val2)
        Case ">="
            compareExpressionAns = (val1 >= val2)
        Case "<"
            compareExpressionAns = (val1 < val2)
        Case "<="
            compareExpressionAns = (val1 <= val2)
        Case Else
            compareExpressionAns = False
  End Select
End Function
Private Function concatRngVal(joinRange As Range, Optional deli As String = ",") As String
 If joinRange.Columns.Count = 1 Then
    concatRngVal = _
    Join(Application.Transpose(joinRange.Value), deli)
 Else
    concatRngVal = _
    Join(Application.Transpose(Application.Transpose(joinRange.Value)), deli)
 End If
End Function

1st input is join range, 2nd is criteria range, 3rd input is comparison operator (=,<>,>,>=,<,<=) ,4th is value to test, 5th is delimiter.

If you do not supply delimiter then default delimiter is comma “,”.

Cell G4 formula =textJoinIf_2($A$3:$A$14,$B$3:$B$14,”=”,F4,”-“)

Only 1st input is mandatory rest is optional.

If you supply only 1st input then it will just perform Textjoin and joins all the values of a range.

=textJoinIf_2($A$3:$A$14) outputs

Fatima,Jade,Jatin,Kshama,Mohan,Prakash,Preeti,Rakul,Sachin,Sohan,Sunny,Tripti

=textJoinIf_2($A$3:$A$14,$B$3:$B$14,”<>“,F4,”-“) will output names not equal to Fit.

Fatima-Jade-Kshama-Prakash-Preeti-Sachin-Sohan-Sunny-Tripti

Cell G3 formula =textJoinIf_2($A$3:$A$6,$B$3:$B$6,”>“,F3,”-“)

TextJoinIf for horizontal table

You can use array formula , textJoinIf_1 UDF, textJoinIf_2 UDF on horizonatal table also.

Cell B5 formula =textJoinIf_2($B$1:$E$1,$B$2:$E$2,”>“,A5,”-“) outputs Three-Four

Know more about VBA Join function

Concatenate and join cells in Excel and concatenate and join in VBA

You can also watch the video version with Hindi speech.

https://youtu.be/t5YKbAUAtLg