Drew,

You could use a User-Defined-Function, definition below. Copy the code and
paste into a standard codemodule of your workbook. Used like:

=Nodes(A2,Sheet1!$B$2:$B$7,Sheet1!$A$2:$A$7)

Where A2 has the reuse code you are interested in, B2:B7 of Sheet1 has the
reuse code table, and A2:A7 of Sheet1 has the Node values.

HTH,
Bernie
MS Excel MVP

Function Nodes(inVal As Range, _
InCode As Range, _
InNode As Range) As Variant

Dim i As Integer

Nodes = "None"

If InCode.Cells.Count < InNode.Cells.Count Then
Nodes = "Unbalanced entry ranges"
Exit Function
End If

If inVal.Cells.Count < 1 Then
Nodes = "Too many key values"
Exit Function
End If

For i = 1 To InCode.Cells.Count
If InCode.Cells(i).Value = inVal.Value Then
If Nodes = "None" Then
Nodes = InNode.Cells(i).Value
Else
Nodes = Nodes & ", " & InNode.Cells(i).Value
End If
End If
Next i

End Function


"AthleteTO" wrote in message
...

OK, this one has me stumped. Anyone with creative ideas? ;)

I have one worksheet with values such as this:


Code:
--------------------
Node ReuseCode
2 C-C01
3 C-C03
4 C-C01
5 C-C03
6 C-C02
7 C-C01
--------------------


On another worksheet, I have the following:


Code:
--------------------
ReuseCode Name Initiating Nodes
C-C01 Policy A
C-C02 Policy B
C-C03 General Information
--------------------


I'd like the Initiating Nodes column to be filled with an array of
values from the first worksheet. The result would look like this:


Code:
--------------------
ReuseCode Name Initiating Nodes
C-C01 Policy A 2, 4, 7
C-C02 Policy B 6
C-C03 General Information 3, 5
--------------------


I've fiddled with formulas, but I am positively stumped. Three cheers
for any bright ideas!

Thanks :)
Drew


--
AthleteTO
------------------------------------------------------------------------
AthleteTO's Profile:
http://www.excelforum.com/member.php...o&userid=15943
View this thread: http://www.excelforum.com/showthread...hreadid=274218