Using Index to return multiple rows of information

[mp3architect]

I have been trying to figure this out for the past few hours with little luck. I have searched through this forum but everything I have tried thus far is just shy of hitting the nail on the head. I am hoping someone can help me out.


I have a large table of many rooms:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Room Name[/TD]
[TD]Area[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]RM01
[/TD]
[TD]Office 1[/TD]
[TD]300[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]RM02[/TD]
[TD]Office 2[/TD]
[TD]300[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]RM03[/TD]
[TD]Office 3[/TD]
[TD]450[/TD]
[TD]Zone 3[/TD]
[/TR]
[TR]
[TD]RM04[/TD]
[TD]Administration[/TD]
[TD]600[/TD]
[TD]Zone 2[/TD]
[/TR]
[TR]
[TD]RM05[/TD]
[TD]*******[/TD]
[TD]200[/TD]
[TD]Zone 1[/TD]
[/TR]
[TR]
[TD]RM06[/TD]
[TD]Conference Room[/TD]
[TD]250[/TD]
[TD]Zone 2[/TD]
[/TR]
</tbody>[/TABLE]
imagine this list goes on for over 200 rooms...



I want to create a table on a separate sheet that will search this information based off which zone it is in and list the rooms by zone:


ZONE 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Room Name[/TD]
[TD]Area[/TD]
[/TR]
[TR]
[TD]RM01
[/TD]
[TD]Office 1[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]RM02[/TD]
[TD]Office 2[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]RM05[/TD]
[TD]*******[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]


Zone2
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Room Name[/TD]
[TD]Area[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]RM04[/TD]
[TD]Administration[/TD]
[TD]600[/TD]
[TD]Zone 2[/TD]
[/TR]
[TR]
[TD]RM06[/TD]
[TD]Conference Room[/TD]
[TD]250[/TD]
[TD]Zone 2
[/TD]
[/TR]
</tbody>[/TABLE]


Zone 3
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Room Name[/TD]
[TD]Area[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]RM03[/TD]
[TD]Office 3[/TD]
[TD]450[/TD]
[TD]Zone 3[/TD]
[/TR]
</tbody>[/TABLE]


The closest answer I have been able to find is replicating an index problem here: http://www.mrexcel.com/forum/excel-questions/546177-vlookup-find-next.html#post2696961.

I have essentially the same issue, where VLookup is perfect, and I am tryign to go to the next line in my original table to generate the new one. However, as I modify the formulas, everything goes to hell.

I am pretty good at basic excel, but very new to index functions. Any help or better direction would be greatly appreciated.

 

[AhoyNC]

See if the following helps.
Change for your ranges.

Formulas in A12, A19 & A26 copy down and across as needed. Be careful on how you lock columns and rows.


These are array formulas and must be entered with

Excel Workbook
	A	B	C	D
1	ID	Room Name	Area	Location
2	RM01	Office 1	300	Zone 1
3	RM02	Office 2	300	Zone 1
4	RM03	Office 3	450	Zone 3
5	RM04	Administration	600	Zone 2
6	RM05	*******	200	Zone 1
7	RM06	Conference Room	250	Zone 2
8
9
10	Zone 1
11	ID	Room Name	Area
12	RM01	Office 1	300
13	RM02	Office 2	300
14	RM05	*******	200
15
16
17	Zone 2
18	ID	Room Name	Area
19	RM04	Administration	600
20	RM06	Conference Room	250
21
22
23
24	Zone 3
25	ID	Room Name	Area
26	RM03	Office 3	450
27
28
CTRL-SHIFT-ENTER.

 

[mp3architect]

Wow! Thank you AhoyNC!!! I have taken the core of this and adjusted it to my entire table and its working so perfectly! I think my issues were dealing with ROW correctly. Thank you so much!

-James

See if the following helps.
Change for your ranges.

Formulas in A12, A19 & A26 copy down and across as needed. Be careful on how you lock columns and rows.


These are array formulas and must be entered with CTRL-SHIFT-ENTER.

*	A	B	C	D
RM01	Office 1	Zone 1
RM02	Office 2	Zone 1
RM03	Office 3	Zone 3
RM04	Administration	Zone 2
RM05	*******	Zone 1
RM06	Conference Room	Zone 2
*	*	*	*
*	*	*	*
Zone 1	*	*	*
*
RM01	Office 1	*
RM02	Office 2	*
RM05	*******	*
*	*	*	*
*	*	*	*
Zone 2	*	*	*
*
RM04	Administration	*
RM06	Conference Room	*
*	*	*	*
*	*	*	*
*	*	*	*
Zone 3	*	*	*
*
RM03	Office 3	*
*	*	*	*
*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:79px;"><col style="width:121px;"><col style="width:68px;"><col style="width:113px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #c0c0c0"]ID[/TD]
[TD="bgcolor: #c0c0c0"]Room Name[/TD]
[TD="bgcolor: #c0c0c0"]Area[/TD]
[TD="bgcolor: #c0c0c0"]Location[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]450[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]600[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]250[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="bgcolor: #c0c0c0"]ID[/TD]
[TD="bgcolor: #c0c0c0"]Room Name[/TD]
[TD="bgcolor: #c0c0c0"]Area[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]300[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]200[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="bgcolor: #c0c0c0"]ID[/TD]
[TD="bgcolor: #c0c0c0"]Room Name[/TD]
[TD="bgcolor: #c0c0c0"]Area[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]

[TD="align: right"]600[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]

[TD="align: right"]250[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="bgcolor: #c0c0c0"]ID[/TD]
[TD="bgcolor: #c0c0c0"]Room Name[/TD]
[TD="bgcolor: #c0c0c0"]Area[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="align: right"]450[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A12 {=IFERROR(INDEX(A$2:A$7,SMALL(IF($D$2:$D$7=$A$10,ROW($D$2:$D$7)-ROW($D$2)+1),ROWS($D$2:D2))),"")} A19 {=IFERROR(INDEX(A$2:A$7,SMALL(IF($D$2:$D$7=$A$17,ROW($D$2:$D$7)-ROW($D$2)+1),ROWS($D$2:D2))),"")} A26 {=IFERROR(INDEX(A$2:A$7,SMALL(IF($D$2:$D$7=$A$24,ROW($D$2:$D$7)-ROW($D$2)+1),ROWS($D$2:D2))),"")} <tbody> </tbody>

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

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AhoyNC]

Your welcome. Glad it worked for you, thanks for the feedback.