Index Array Issues - PLEASE HELP!

[UrbanJungleFashion]

Hello All.

I can't for the life of me figure out how to do an index array. I have been all over YouTube and Chandoo and I can't figure it out and I have a very simple one.

In column B3 I want to enter the Region name in and in rows B8-B22 list the stores that fall under that region.

I tried to use the formula:
=INDEX($D$2:$D$27,SMALL(IF($E$2:$E$27=$B$3,ROW($E$2:$E$27)-ROW($E$2)+1),ROWS($B$8:B8)),"")

But that's not working correctly.

Can someone tell me what the correct formula should be and where I'm going wrong?

Example:

A	B		C Store	D Region
			0004	212
	212		0008	212
			0012	212
			0016	212
			0020	212
	STORES		0024	212
1	#VALUE!		0003	445
2	#NUM!		0007	445
3	#NUM!		0011	445
4	#NUM!		0015	445
5	#NUM!		0019	445
6	#NUM!		0023	445
7	#NUM!		0002	451
8	#NUM!		0006	451
9	#NUM!		0010	451
10	#NUM!		0014	451
11	#NUM!		0018	451
12	#NUM!		0022	451
13	#NUM!		0026	451
14	#NUM!		0001	551
15	#NUM!		0005	551
			0009	551
			0013	551
			0017	551
			0021	551
			0025	551

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[AhoyNC]

Try:
Also, make sure you enter with CTRL-SHIFT-ENTER

=IFERROR(INDEX($D$2:$D$27,SMALL(IF($E$2:$E$27=$B$3,ROW($E$2:$E$27)-ROW($E$2)+1),ROWS($B$8:B8))),"")

 

[UrbanJungleFashion]

Try:
Also, make sure you enter with CTRL-SHIFT-ENTER

=IFERROR(INDEX($D$2:$D$27,SMALL(IF($E$2:$E$27=$B$3,ROW($E$2:$E$27)-ROW($E$2)+1),ROWS($B$8:B8))),"")

Thanks for your help but I keep getting a value error.

 

[AhoyNC]

It's working for me. See below.
Make sure you enter the formula with CTRL-SHIFT-ENTER (command return on MAC). If done right Excel will place {} around the formula.
If you have just pressed ENTER then press F2 to edit and then CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1				Store	Region
2				4	212
3		212		8	212
4				12	212
5				16	212
6				20	212
7		STORES		24	212
8	1	4		3	445
9	2	8		7	445
10	3	12		11	445
11	4	16		15	445
12	5	20		19	445
13	6	24		23	445
14	7			2	451
15	8			6	451
16	9			10	451
17	10			14	451
18	11			18	451
19	12			22	451
20	13			26	451
21	14			1	551
22	15			5	551
23				9	551
24				13	551
25				17	551
26				21	551
27				25	551
Sheet

 

[UrbanJungleFashion]

Thank you so much for this! I figured out was I was going wrong. I had half the numbers as text and the other half as numbers. So I formatted all the data to read as numbers and it worked.

Thank you so much for the formula!!!

It's working for me. See below.
Make sure you enter the formula with CTRL-SHIFT-ENTER (command return on MAC). If done right Excel will place {} around the formula.
If you have just pressed ENTER then press F2 to edit and then CTRL-SHIFT-ENTER.

*	A	B	C	D	E
1	*	*	*	Store	Region
2	*	*	*	4	212
3	*	212	*	8	212
4	*	*	*	12	212
5	*	*	*	16	212
6	*	*	*	20	212
7	*	STORES	*	24	212
8	1	4	*	3	445
9	2	8	*	7	445
10	3	12	*	11	445
11	4	16	*	15	445
12	5	20	*	19	445
13	6	24	*	23	445
14	7	*	*	2	451
15	8	*	*	6	451
16	9	*	*	10	451
17	10	*	*	14	451
18	11	*	*	18	451
19	12	*	*	22	451
20	13	*	*	26	451
21	14	*	*	1	551
22	15	*	*	5	551
23	*	*	*	9	551
24	*	*	*	13	551
25	*	*	*	17	551
26	*	*	*	21	551
27	*	*	*	25	551

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:67px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B8 {=IFERROR(INDEX($D$2:$D$27,SMALL(IF($E$2:$E$27=$B$3,ROW($E$2:$E$27)-ROW($E$2)+1),ROWS($B$8:B8))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4