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.
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,613
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,613
Office Version
  1. 365
Platform
  1. Windows
Your welcome. Glad it worked for you, thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top