Extracting data from table with mismatched rows and columns

[Tdw1990]

Hello,
I'm trying to figure out how I can extract data from a table that has multiple parent rows and then lists values on a separate row and column. For example, I want to return the value of Total Occupied % that corresponds to 10000-Laurete RE Investors,LP,Houston. I'm sure there is a similar post about this, I just cant think of what the formulas are called. Any help is appreciated

OccupancySummary07_20_2021.xlsx
	B	C	D	E	F	G	H	I	J
3	Lease Type	# of Units	Total	Rentable	Total Rent/	Occupied	Total Rent/	Occupied	6 Month
4			Rent	Area	Rentable	Area	Occupied	%	RollOver
5					Area		Area
6	10000 - Laureate RE Investors, LP,Houston
7	N/A	15.00	0.00	18,084.00	0.00	0.00	0.00	0.00	0.00
8	Office Net	43.00	301,832.07	132,453.00	2.28	132,355.00	2.28	99.93	9,473.00
9	Total	58.00	301,832.07	150,537.00	2.01	132,355.00	2.28	87.92	9,473.00
10
11	10343 - SH Crossing I LP,Houston
12	N/A	7.00	0.00	87,932.00	0.00	0.00	0.00	0.00	0.00
13	Office Net	7.00	99,228.81	71,243.00	1.39	71,243.00	1.39	100.00	0.00
14	Total	14.00	99,228.81	159,175.00	0.62	71,243.00	1.39	44.76	0.00
15
16	10706 - Fuller Craighead, LLC,Houston
17	Office Net	1.00	9,000.00	20,000.00	0.45	20,000.00	0.45	100.00	20,000.00
18	Total	1.00	9,000.00	20,000.00	0.45	20,000.00	0.45	100.00	20,000.00
19
20	11200 - Fuller Westchase Place,Houston
21	N/A	18.00	0.00	79,318.00	0.00	0.00	0.00	0.00	0.00
22	Office Net	14.00	87,056.20	72,726.00	1.20	72,726.00	1.20	100.00	1,301.00
23	Total	32.00	87,056.20	152,044.00	0.57	72,726.00	1.20	47.83	1,301.00
24
25	11205 - HMC Fuller Main Park, LLC,Houston
26	N/A	4.00	0.00	41,080.00	0.00	0.00	0.00	0.00	0.00
27	Office Net	12.00	57,562.66	71,890.00	0.80	71,890.00	0.80	100.00	11,873.00
28	Total	16.00	57,562.66	112,970.00	0.51	71,890.00	0.80	63.64	11,873.00
29
30	1132 - Fuller Everman LP,Houston
31	Office Net	1.00	26,352.00	0.00	0.00		0.00	0.00	0.00
32	Total	1.00	26,352.00	0.00	0.00	0.00	0.00	0.00	0.00
33
34	12654 - Fuller Goar, LLC,Houston
35	N/A	1.00	0.00	89.00	0.00	0.00	0.00	0.00	0.00
36	Office Net	11.00	28,036.94	42,813.00	0.65	42,813.00	0.65	100.00	0.00
37	Total	12.00	28,036.94	42,902.00	0.65	42,813.00	0.65	99.79	0.00
38
39	12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston
40	N/A	1.00	0.00	1,990.00	0.00	0.00	0.00	0.00	0.00
41	Office Net	16.00	41,260.24	43,908.00	0.94	43,908.00	0.94	100.00	3,776.00
42	Total	17.00	41,260.24	45,898.00	0.90	43,908.00	0.94	95.66	3,776.00
43
44	1450 - HMC Fuller Westbelt, LLC,Houston
45	N/A	11.00	0.00	24,315.00	0.00	0.00	0.00	0.00	0.00
46	Office Net	15.00	47,139.03	41,304.00	1.14	41,139.00	1.15	99.60	1,970.00
47	Total	26.00	47,139.03	65,619.00	0.72	41,139.00	1.15	62.69	1,970.00
Report1

 

[Toadstool]

Hi TWD1990,

With your 365 version there are more options but my Excel 2016 approach would be:

TDW1990.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		Lease Type	# of Units	Total	Rentable	Total Rent/	Occupied	Total Rent/	Occupied	6 Month
4				Rent	Area	Rentable	Area	Occupied	%	RollOver
5						Area		Area				Lease Type	Total
6		10000 - Laureate RE Investors, LP,Houston										10000 - Laureate RE Investors, LP,Houston	87.92
7		N/A	15	0	18084	0	0	0	0	0		10343 - SH Crossing I LP,Houston	44.76
8		Office Net	43	301832.1	132453	2.28	132355	2.28	99.93	9473		10706 - Fuller Craighead, LLC,Houston	100
9		Total	58	301832.1	150537	2.01	132355	2.28	87.92	9473		11200 - Fuller Westchase Place,Houston	47.83
10												11205 - HMC Fuller Main Park, LLC,Houston	63.64
11		10343 - SH Crossing I LP,Houston										1132 - Fuller Everman LP,Houston	0
12		N/A	7	0	87932	0	0	0	0	0		12654 - Fuller Goar, LLC,Houston	99.79
13		Office Net	7	99228.81	71243	1.39	71243	1.39	100	0		12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston	95.66
14		Total	14	99228.81	159175	0.62	71243	1.39	44.76	0		1450 - HMC Fuller Westbelt, LLC,Houston	62.69
15
16		10706 - Fuller Craighead, LLC,Houston
17		Office Net	1	9000	20000	0.45	20000	0.45	100	20000
18		Total	1	9000	20000	0.45	20000	0.45	100	20000
19
20		11200 - Fuller Westchase Place,Houston
21		N/A	18	0	79318	0	0	0	0	0
22		Office Net	14	87056.2	72726	1.2	72726	1.2	100	1301
23		Total	32	87056.2	152044	0.57	72726	1.2	47.83	1301
24
25		11205 - HMC Fuller Main Park, LLC,Houston
26		N/A	4	0	41080	0	0	0	0	0
27		Office Net	12	57562.66	71890	0.8	71890	0.8	100	11873
28		Total	16	57562.66	112970	0.51	71890	0.8	63.64	11873
29
30		1132 - Fuller Everman LP,Houston
31		Office Net	1	26352	0	0		0	0	0
32		Total	1	26352	0	0	0	0	0	0
33
34		12654 - Fuller Goar, LLC,Houston
35		N/A	1	0	89	0	0	0	0	0
36		Office Net	11	28036.94	42813	0.65	42813	0.65	100	0
37		Total	12	28036.94	42902	0.65	42813	0.65	99.79	0
38
39		12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston
40		N/A	1	0	1990	0	0	0	0	0
41		Office Net	16	41260.24	43908	0.94	43908	0.94	100	3776
42		Total	17	41260.24	45898	0.9	43908	0.94	95.66	3776
43
44		1450 - HMC Fuller Westbelt, LLC,Houston
45		N/A	11	0	24315	0	0	0	0	0
46		Office Net	15	47139.03	41304	1.14	41139	1.15	99.6	1970
47		Total	26	47139.03	65619	0.72	41139	1.15	62.69	1970
Sheet1

Cell Formulas
Range	Formula
L6:L47	L6	=IFERROR(INDEX($B$6:$B$9999,AGGREGATE(15,6,ROW($B$6:$B$9999)-ROW($B$5)/(($B$6:$B$9999<>"")*($C$6:$C$9999="")),ROW()-ROW($L$5))),"")
M6:M47	M6	=IFERROR(INDEX($I$6:$I$9999,AGGREGATE(15,6,ROW($B$6:$B$9999)-ROW($B$5)/(($B$6:$B$9999="Total")),ROW()-ROW($L$5))),"")

 

[Tdw1990]

Hi TWD1990,

With your 365 version there are more options but my Excel 2016 approach would be:

TDW1990.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3		Lease Type	# of Units	Total	Rentable	Total Rent/	Occupied	Total Rent/	Occupied	6 Month
4				Rent	Area	Rentable	Area	Occupied	%	RollOver
5						Area		Area				Lease Type	Total
6		10000 - Laureate RE Investors, LP,Houston										10000 - Laureate RE Investors, LP,Houston	87.92
7		N/A	15	0	18084	0	0	0	0	0		10343 - SH Crossing I LP,Houston	44.76
8		Office Net	43	301832.1	132453	2.28	132355	2.28	99.93	9473		10706 - Fuller Craighead, LLC,Houston	100
9		Total	58	301832.1	150537	2.01	132355	2.28	87.92	9473		11200 - Fuller Westchase Place,Houston	47.83
10												11205 - HMC Fuller Main Park, LLC,Houston	63.64
11		10343 - SH Crossing I LP,Houston										1132 - Fuller Everman LP,Houston	0
12		N/A	7	0	87932	0	0	0	0	0		12654 - Fuller Goar, LLC,Houston	99.79
13		Office Net	7	99228.81	71243	1.39	71243	1.39	100	0		12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston	95.66
14		Total	14	99228.81	159175	0.62	71243	1.39	44.76	0		1450 - HMC Fuller Westbelt, LLC,Houston	62.69
15
16		10706 - Fuller Craighead, LLC,Houston
17		Office Net	1	9000	20000	0.45	20000	0.45	100	20000
18		Total	1	9000	20000	0.45	20000	0.45	100	20000
19
20		11200 - Fuller Westchase Place,Houston
21		N/A	18	0	79318	0	0	0	0	0
22		Office Net	14	87056.2	72726	1.2	72726	1.2	100	1301
23		Total	32	87056.2	152044	0.57	72726	1.2	47.83	1301
24
25		11205 - HMC Fuller Main Park, LLC,Houston
26		N/A	4	0	41080	0	0	0	0	0
27		Office Net	12	57562.66	71890	0.8	71890	0.8	100	11873
28		Total	16	57562.66	112970	0.51	71890	0.8	63.64	11873
29
30		1132 - Fuller Everman LP,Houston
31		Office Net	1	26352	0	0		0	0	0
32		Total	1	26352	0	0	0	0	0	0
33
34		12654 - Fuller Goar, LLC,Houston
35		N/A	1	0	89	0	0	0	0	0
36		Office Net	11	28036.94	42813	0.65	42813	0.65	100	0
37		Total	12	28036.94	42902	0.65	42813	0.65	99.79	0
38
39		12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston
40		N/A	1	0	1990	0	0	0	0	0
41		Office Net	16	41260.24	43908	0.94	43908	0.94	100	3776
42		Total	17	41260.24	45898	0.9	43908	0.94	95.66	3776
43
44		1450 - HMC Fuller Westbelt, LLC,Houston
45		N/A	11	0	24315	0	0	0	0	0
46		Office Net	15	47139.03	41304	1.14	41139	1.15	99.6	1970
47		Total	26	47139.03	65619	0.72	41139	1.15	62.69	1970
Sheet1

Cell Formulas
Range	Formula
L6:L47	L6	=IFERROR(INDEX($B$6:$B$9999,AGGREGATE(15,6,ROW($B$6:$B$9999)-ROW($B$5)/(($B$6:$B$9999<>"")*($C$6:$C$9999="")),ROW()-ROW($L$5))),"")
M6:M47	M6	=IFERROR(INDEX($I$6:$I$9999,AGGREGATE(15,6,ROW($B$6:$B$9999)-ROW($B$5)/(($B$6:$B$9999="Total")),ROW()-ROW($L$5))),"")

Can you explain what this function is doing? To me, it seems the aggregate function would be returning the n-th smallest value corresponding to "Total" in the Occupied % column

 

[Crystalyzer]

I approached this a little differently and made it dynamic so that the lookup column could change.

NOTE: in order to do that I had to put the column names into one cell (which is really best practice) and deleted rows 2 & 3.

Solutions 20210719.003.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Lease Type	# of Units	Total Rent	Rentable Area	Total Rent/ Rentable Area	Occupied Area	Total Rent/ Occupied Area	Occupied %	6 Month RollOver
2		10000 - Laureate RE Investors, LP,Houston										MaxNumRows	5
3		N/A	15	0	18084	0	0	0	0	0		lkupRowTxt	11200 - Fuller Westchase Place,Houston
4		Office Net	43	301832.1	132453	2.28	132355	2.28	99.93	9473		lkupColTxt	6 Month RollOver
5		Total	58	301832.1	150537	2.01	132355	2.28	87.92	9473		Result	1301
6
7		10343 - SH Crossing I LP,Houston
8		N/A	7	0	87932	0	0	0	0	0
9		Office Net	7	99228.81	71243	1.39	71243	1.39	100	0
10		Total	14	99228.81	159175	0.62	71243	1.39	44.76	0
11
12		10706 - Fuller Craighead, LLC,Houston
13		Office Net	1	9000	20000	0.45	20000	0.45	100	20000
14		Total	1	9000	20000	0.45	20000	0.45	100	20000
15
16		11200 - Fuller Westchase Place,Houston
17		N/A	18	0	79318	0	0	0	0	0
18		Office Net	14	87056.2	72726	1.2	72726	1.2	100	1301
19		Total	32	87056.2	152044	0.57	72726	1.2	47.83	1301
20
21		11205 - HMC Fuller Main Park, LLC,Houston
22		N/A	4	0	41080	0	0	0	0	0
23		Office Net	12	57562.66	71890	0.8	71890	0.8	100	11873
24		Total	16	57562.66	112970	0.51	71890	0.8	63.64	11873
25
26		1132 - Fuller Everman LP,Houston
27		Office Net	1	26352	0	0		0	0	0
28		Total	1	26352	0	0	0	0	0	0
29
30		12654 - Fuller Goar, LLC,Houston
31		N/A	1	0	89	0	0	0	0	0
32		Office Net	11	28036.94	42813	0.65	42813	0.65	100	0
33		Total	12	28036.94	42902	0.65	42813	0.65	99.79	0
34
35		12705 - 12705 S Kirkwood (Fuller NWSW, LLC),Houston
36		N/A	1	0	1990	0	0	0	0	0
37		Office Net	16	41260.24	43908	0.94	43908	0.94	100	3776
38		Total	17	41260.24	45898	0.9	43908	0.94	95.66	3776
39
40		1450 - HMC Fuller Westbelt, LLC,Houston
41		N/A	11	0	24315	0	0	0	0	0
42		Office Net	15	47139.03	41304	1.14	41139	1.15	99.6	1970
43		Total	26	47139.03	65619	0.72	41139	1.15	62.69	1970
Mismatched Rows Cols

Cell Formulas
Range	Formula
M5	M5	=INDEX($B$1:$J$43,MATCH(lkupRowTxt,$B$1:$B$43,0)+MATCH("Total",OFFSET(INDIRECT("B" & MATCH(lkupRowTxt,$B$1:$B$43,0)),1,0,MaxNumRows,1),0),MATCH(lkupColTxt,$B$1:$J$1,0))

Named Ranges
Name	Refers To	Cells
lkupColTxt	='Mismatched Rows Cols'!$M$4	M5
lkupRowTxt	='Mismatched Rows Cols'!$M$3	M5
MaxNumRows	='Mismatched Rows Cols'!$M$2	M5

 

[Toadstool]

Can you explain what this function is doing? To me, it seems the aggregate function would be returning the n-th smallest value corresponding to "Total" in the Occupied % column

Your example was the first Lease Type so I assumed you may want to see all Lease Types and their Total under column I. If that's not the case then you can do a MATCH on the Lease Type to find its row (in this case row 6) then use AGGREGATE to find the first "Total" with a higher row number than the MATCH result.

My column L AGGREGATE is looking for the nth row number where column B isn't empty but column C is empty. The ROW()-ROW($L$5) is the k parameter which for row 6 returns a 1 so it looks for the 1st entry where that is true.
By the time we get to row 14 then ROW()-ROW($L$5) returns 9 so it looks for the 9th lowest row number where that's true, which is row 44 so it returns "1450 - HMC Fuller Westbelt, LLC,Houston".

The column M is the same formula but looking for "Total" so as long as all your data matches the example you supplied then the row 14 formula will find the 9th "Total" in row 47 and return 62.69.