Dynamically Generating a list

Try...

=IFERROR(INDEX(B:B,SMALL(IF($A$1:$A$100=$E$1,ROW($A$1:$A$100)),ROW(A1))),"")

Drag down until you get blanks. E1 = ABC

IMPORTANT

• This is an array formula
• Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
• If entered correctly, the formula will be enclosed in {brackets}
• Do not enter the {brackets} manually

Based on your description above...

=IFERROR(INDEX(Sheet2!$B:$B,SMALL(IF(Sheet2!$A$1:$A$100=$B$2,ROW(Sheet2!$A$1:$A$100)),ROW(Sheet2!A1))),"")

If this does not work again then maybe attach a sample file. Remember, enter as a CSE formula

Assuming your data is in the range A2:B100.

Try this array formula** entered in A10:

=IFERROR(INDEX(Sheet2!B:B,SMALL(IF(Sheet2!A$2:A$100=B$2,ROW(Sheet2!A$2:A$100)),ROWS(A$10:A10))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Hi Tony,

I see you've used Rows as the k argument for the Small function versus Row.

I seem to recall you saying you use Rows instead of Row to guard against the deletion of rows? Is this right?

It accounts for row insertions before the row where the 1st formula is entered.

It's not 100% "bulletproof" but it's more robust compared to the ROW function.

There's no such thing as:

It kind of works....

It either works or it doesn't!

Ok, give me 10 minutes and I'll post a sample file that demonstrates this.

Thanks Tony. I have a workbook already prepared...hope that's alright.

OK, here's the sample file:

JayReina.xlsx

Make sure you enter the formula as an array formula.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

Originally Posted by jeffreybrown

Thanks Tony. I have a workbook already prepared...hope that's alright.

Ok, I'll butt out!

No way, I've learned so much from watching your impressive formula work.

I see the "happy face" so we must have made some progress!

Originally Posted by JayReina

I am having an issue but is not your formula...is that my Excel 2010 is not making it into an Array when I hit CTRL, Shift, Enter...

If it is not making it into an array and producing a result, can you tell us what it is producing? A blank, value error, wrong result...