Thanks to all for the help. I really appreciate it. I got the last one from
from Teethless Mama running in my application. But FYI, I found another nice
way to do it with an advanced filter and VBA. Another excel wizard had this
one on their website thata I downloaded a while ago and now can't find where
I got it. In any case Here's the code in case you are interested. Runs super
fast in my overloaded spreadsheet:

Sub BuildKomp()
'calculate criteria cell in case calculation mode is manual
Sheets("Build").Range("Criteria").Calculate
Worksheets("MRP").Range("BOMKomp") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Build").Range("Criteria"), _
CopyToRange:=Range("BuildKom"), Unique:=False
'calculate summary total in case calculation mode is manual
Sheets("build").Calculate
End Sub

I know this code could probably be tightened up a bit but admittedly I am no
VBA guru myself. It works and I don't have to recalc the whole workbook to
grab the data.

Anyway, I have another application that can use the slick lookups you have
posted here.
Thanks again,
RDW

"RD Wirr" wrote:

I have a list of values like this:
A
A1
A2
A3
A4
B
B1
B1
B3
C
C1
I need to have an array of formulas that looks up the instance of one of the
alpha characters (referencing the value in another cell), and then populate a
column of cells with the values that include the referenced Alpha for
example, if the reference is B, the lookups would populate my column of cells
with:
B
B1
B2
B3
The qty of values included with each alpha is variable.

Does anyone know an efficient way to do this?
Thanks in advance,
RDW