Get Group Name based on Code

[Rudi]

Hi,

I need assistance with a formula or with a UDF to get the group name into a column based on a code supplied. The code supplied can be found in a column and the formula should return the main group name based on the location of the found code. It's simple to do with a macro, but in this case I need a formula or a UDF.

Any assistance will be appreciated.
TX

Get Group.xlsx

[HansV]

There may be a more efficient way, but here is an array formula for B2, to be confirmed with Ctrl+Shift+Enter:

=INDEX($D$1:$D$29,MAX((OFFSET($D$1,0,0,MATCH(A2,$D$1:$D$29,0),1)="")*ROW(OFFSET($D$1,0,0,MATCH(A2,$D$1:$D$29,0),1)))+1)

Adjust the range $D$1:$D$29 as needed, then fill down.

Explanation:

p = MATCH(A2,$D$1:$D$29,0) is the row number where the code is found in column D.
r = OFFSET($D$1,0,0,p,1) is the range in column D from row 1 to row p.
a1 = (r="") is an array of TRUE/FALSE values: TRUE if a cell in r is empty, FALSE otherwise.
a2 = ROW(r) is the array of the row numbers of r.
a = a1*a2 is an array of numbers: the row number for empty cells in r, 0 otherwise
m = MAX(a) is the highest row number of an empty cell in r.
INDEX($D$1:$D$29,m+1) is the cell below the last empty cell, i.e. the cell with the group name.

[Rudi]

Geez... I dunno how you create these array formulas.
I can step through them with the Evaluate Tool, and I can see how they are being processed, and strangely, they make sense, however, to build it myself... Where does one start?!! Many TX Hans, the formula is working well in the actual file too.

Thanks for the Explanation.
It's useful to map through the process as you've written it, and Evaluate was also nice and visual to step through.

Request: (ONLY if you have time or are bored)!!
Could you humour me with a UDF as well?
I can create simple function procedures, but I'm interested to see how to put one together that uses ranges as arguments.
Please don't fuss with this if you have other things to do.

[HansV]

I started by building each "step" of the formula in a separate column, more or less along the lines of the explanation in my previous reply.
When I got it working, I copied each step into the next one, until I ended up with a single formula.

I'll look into a UDF later today.

[HansV]

Here is a UDF that will return the group name:

Code: Select all

Function GroupName(Code As String) As String
    Dim cel As Range
    If Code <> "" Then
        Set cel = Range("D:D").Find(What:=Code, LookAt:=xlWhole)
        If Not cel Is Nothing Then
            GroupName = cel.End(xlUp).Value
        End If
    End If
End Function

This version returns an empty string if the code is blank, or if there is no match.
If you want to return #N/A if there is no match, change the function as follows:

Code: Select all

Function GroupName(Code As String) As Variant
    Dim cel As Range
    If Code = "" Then
        GroupName = ""
    Else
        Set cel = Range("D:D").Find(What:=Code, LookAt:=xlWhole)
        If Not cel Is Nothing Then
            GroupName = cel.End(xlUp).Value
        Else
            GroupName = CVErr(xlErrNA)
        End If
    End If
End Function

[Rudi]

I appreciate the extra request.
Many TX Hans.
and

[HansV]

If you want to specify the column containing the data as argument, you can do it like this:

Code: Select all

Function GroupName(Code As String, DataColumn As Range) As Variant
    Dim cel As Range
    If Code = "" Then
        GroupName = ""
    Else
        Set cel = DataColumn.Find(What:=Code, LookAt:=xlWhole)
        If Not cel Is Nothing Then
            GroupName = cel.End(xlUp).Value
        Else
            GroupName = CVErr(xlErrNA)
        End If
    End If
End Function

The formula then becomes =GroupName(A2,$D$2:$D$29)

[Rudi]

As a matter of fact, I've already edited your code to do exactly that.
I called the argument: Lookup_Array As Range and renamed the function to GetFundGroup.
The rest is exactly as your example.

It is working really well and is much more user friendly for the person I'm passing it on to.

Function.png