1

I have the following scenario (in excel 2010):

  • 2 columns, one with a list of User names and the other with their related email addresses (I assigned them the named ranges: UserName and UserEmail).
  • a cell ("D2") containing a list of unique usernames (selected by the user) from UserName, separated by "," (ex: "user5, user3, user9").
  • The problem I need to solve is to look in UserEmail for the email addresses, corresponding to the users selected, create a string with them, separated by "," and assign it to a cell (ex: "u5@email.com, u3@email.com, u9@email.com" in D5)

Everything seemed to work fine until I tried to pass, to the function I use to merge arrays into a string (function "Merge"), the following array formula:

    {=Merge(INDEX(UserEmail,MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",199)),(ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1))-(ROW(D2)))*199+((ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)))=(ROW(D2))),199)),UserName,0)))}

I get the #VALUE! error. I checked the formula with shift+Ctrl+Enter in adjacent cells and it gives the correct email addresses. The problem is in the array I'm trying to pass.

Here is the function:

    Public Function Merge(arr As Variant) As String
    Dim tmpArr
        tmpArr = Application.WorksheetFunction.Transpose((arr))
        Merge = Join(tmpArr, ", ")
    End Function

I've been trying to find out why I get this error and I really have no clue. I wish someone could help me. What really confuses me is that if I pass the array formula until the MATCH part, it works as expected:

    {=Merge(MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",199)),(ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1))-(ROW(D2)))*199+((ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)))=(ROW(D2))),199)),UserName,0))}

and I get "5, 3, 9" (the correct indexes of the selected users in UserName). So what happens to the array when I add the INDEX part? I also tried to use VLOOKUP and I got the same error:

    {=Merge(VLOOKUP(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",199)),(ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1))-(ROW(D2)))*199+((ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)))=(ROW(D2))),199)),A2:B11,2))}

At the moment I resorted to use a work around, which I don't really like:

    {="B" & SUBSTITUTE(Merge(MATCH(TRIM(MID(SUBSTITUTE(D2,",",REPT(" ",199)),(ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1))-(ROW(D2)))*199+((ROW(OFFSET(D2,,,LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1)))=(ROW(D2))),199)),UserName,0)+1),", "," & "", "" & B")}

That yields "B6 & ", " & B4 & ", " & B10" (I added 1 to the indexes because in A1 and B1 there are the headers) and then pass it to an Evaluate function:

    Function ev(r As String) As Variant
        ev = Evaluate(r)
    End Function

so that I can use the formula =ev(D4) in "D5" and get the email addresses I need. I really wish someone could tell me a way to pass the initial formula, though, in order to avoid this workaround, or suggest me a more elegant solution. Thank you.

Marte
  • 11
  • 3

2 Answers2

0

Since you are using VBA UDF anyway, you can incorporate rest of the formula within UDF like below.

Public Function ConcatIf(rngCrit As Range, strDelim As String, rngToLookup As Range, rngToReturn As Range) As String
    Dim rng As Range
    Dim i As Long
    Dim varChkArr
    varChkArr = Split(rngCrit.Cells(1, 1).Value, strDelim)
    For i = LBound(varChkArr) To UBound(varChkArr)
        If IsNumeric(Application.Match(Trim(varChkArr(i)), rngToLookup, 0)) Then
            ConcatIf = ConcatIf & " " & Application.Index(rngToReturn, Application.Match(Trim(varChkArr(i)), rngToLookup, 0))
        End If
    Next i
    ConcatIf = Replace(Trim(ConcatIf), " ", ", ")
End Function

Then to implement it, formula would look like below:

=ConcatIf(D2,",",UserName,UserEmail)

shrivallabha.redij
  • 5,832
  • 1
  • 12
  • 27
  • Thank you shrivallabha.redij, that's working for me and suits my needs. My first idea was to avoid UDF at all, that's why I got stuck with the array formula. Then I couldn't make it and tried with the Merge function that I already used in other projects. I'd still like to understand what is the correct way to pass the array formula, thought. Would be great if someone could give me some explanations on that, even if your solution is already fine. Thank you. – Marte Jun 03 '21 at 13:12
0

After a deeper search I found the answer to my question in this great post by @jeffreyweir: https://stackoverflow.com/a/47189998/16110499 With the amazing solution he suggested, I was able to solve my problem. To summarize it, he explains that INDEX function passes only the first element to another function. However, there are ways to “de-reference” INDEX so that you can then use its results directly in other formulas; one of those methods is to amend the INDEX function slightly by adding an extra bit to encase the range argument. Here is an example:

Original formula (not working properly): =SUM(INDEX(Data,Elements))

New formula (working): =SUM(INDEX(Data, N(IF({1},Elements))))

I added this, just in case someone might encounter the same issue :)

Marte
  • 11
  • 3