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:
IDRoom NameAreaLocation
RM01
Office 1300Zone 1
RM02Office 2300Zone 1
RM03Office 3450Zone 3
RM04Administration600Zone 2
RM05*******200Zone 1
RM06Conference Room250Zone 2

<tbody>
</tbody>
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
IDRoom NameArea
RM01
Office 1300
RM02Office 2300
RM05*******200

<tbody>
</tbody>


Zone2
IDRoom NameAreaLocation
RM04Administration600Zone 2
RM06Conference Room250Zone 2

<tbody>
</tbody>


Zone 3
IDRoom NameAreaLocation
RM03Office 3450Zone 3

<tbody>
</tbody>


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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,930
Office Version
  1. 365
Platform
  1. Windows
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.
 

mp3architect

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

<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>
</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
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,930
Office Version
  1. 365
Platform
  1. Windows
Your welcome. Glad it worked for you, thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,956
Members
430,330
Latest member
drAli77

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
Top