Humble09 wrote:
I have excel 2007 and want to group the list in the 'KEYS' column so that it
reference the name of the person who has the keys.

LAST NAME FIRST NAME KEYS
Sample Mrs. C0xxE-4
C0xxE-8
XX28E-15

Smith John OYC-56
GX-8
COX-A
GX3-1x
GX0A-X

What I would like to do is have 'KEYS' C0xxE-4, C0xxE-8, & XX28E-15
associated with Ms. Sample without having to put Ms. Sample's name down for
each key.
My end goal is to filter the keys with COxxE-8 and to show along with Ms.
Sample's information. ( I really don't want to have 10 'KEYS' columns).

When I tried to filter the KEYS Column only the key comes up and not the
person's name. I have tried to group them but that did not work, as well as
auto outline.

Please Help



Assume your data with labels in A1:C10. Place a key to search for in F4.

First name is given by:
=INDEX($B$1:$B$10,LARGE(N(OFFSET($B$1,0,0,MATCH(F4 ,$C$1:$C$10,0),1)<"")*ROW(INDIRECT("1:"&MATCH(F4, $C$1:$C$10,0))),1))

Last name is given by:
=INDEX($A$1:$A$10,LARGE(N(OFFSET($A$1,0,0,MATCH(F4 ,$C$1:$C$10,0),1)<"")*ROW(INDIRECT("1:"&MATCH(F4, $C$1:$C$10,0))),1))

These are array formulas, so commit by pressing Ctrl+Shift+Enter, not
just Enter.