If the service codes are grouped together like your sample:

Enter this formula in B1 and copy down to a number of cells that equals the
maximum count of any single service. For example, in your sample data AMMS
and Exec both appear the most times, 2. So you'd need to copy the formula to
at least 2 cells.

=IF(ROWS(B$1:B1)<=COUNTIF(J$2:J$6,A$1),INDEX(K$2:K $6,MATCH(A$1,J$2:J$6,0)+ROWS(B$1:B1)-1),"")

--
Biff
Microsoft Excel MVP


"HAZBONES" wrote in message
...
I have two data source columns named Service (J1) and FCP (K1). Service
column is text data and FCP is number data like this:

Service FCP
AMMS 70
AMMS 13
Medical 20
Exec 6
Exec 19

*notice that there can be more than one FCP number to a service

I have a drop down box at (A1). The drop down box works fine, the user
clicks on it and is given a list of the Services that he can pick from.

Beside the drop down list in Column B I want to have appear all of the FCP
numbers (not a total of them but how they appear in the FCP source coulmn)
that relate to the Service that was selected from the drop down box like
this:

[Column A] [Column B]
Dropdown FCP
*Service Selected* FCP1
FCP2
FCP3

I have been racking my brain for 3 days on this. Can anyone tell me how
this
could be done?