Using Index to return multiple rows of information

mp3architect

New Member
Joined
Sep 8, 2014
Messages
2
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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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
ABCD
1IDRoom NameAreaLocation
2RM01Office 1300Zone 1
3RM02Office 2300Zone 1
4RM03Office 3450Zone 3
5RM04Administration600Zone 2
6RM05*******200Zone 1
7RM06Conference Room250Zone 2
8
9
10Zone 1
11IDRoom NameArea
12RM01Office 1300
13RM02Office 2300
14RM05*******200
15
16
17Zone 2
18IDRoom NameArea
19RM04Administration600
20RM06Conference Room250
21
22
23
24Zone 3
25IDRoom NameArea
26RM03Office 3450
27
28
CTRL-SHIFT-ENTER.
 
Upvote 0
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.

*ABCD
RM01Office 1Zone 1
RM02Office 2Zone 1
RM03Office 3Zone 3
RM04AdministrationZone 2
RM05*******Zone 1
RM06Conference RoomZone 2
****
****
Zone 1***
*
RM01Office 1*
RM02Office 2*
RM05********
****
****
Zone 2***
*
RM04Administration*
RM06Conference Room*
****
****
****
Zone 3***
*
RM03Office 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
CellFormula
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
 
Upvote 0
Your welcome. Glad it worked for you, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,731
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top