Formula for Returning an Array

I have a large residential project where I need to split out aspects of each unit type across multiple buildings. For simplicity sake, I have unit types A, B, and C that have 2, 3, and 5 bedrooms respectively. I need to find out how many bedrooms are in Buildings X, Y, and Z knowing there are so many of each unit in each building as shown in the tables below:

	Unit Type A	Unit Type B	Unit Type C	Building X	Building Y	Building Z
Bedrooms	2	3	5	x	y	z
# of Unit Types per Building	Building X	Building Y	Building Z
Unit Type A	20	30	40
Unit Type B	10	25	60
Unit Type C	5	20	10

I've tried all manner of sumproduct, vlookup, hlookup, row, column, mmult, match, and index formulas to find the number of bedrooms in each building based on referencing the two tables and have had no luck. I am hoping someone here might be able to help me.

The best I understand it, to find (x,y,z) I need some sort of array formula that references how many bedrooms are in each unit type and how many unit types are in each building. The closest I got was with this formula in cell E2 for the building X bedrooms:

=SUMPRODUCT(B2:D2,VLOOKUP(TRANSPOSE(B1:D1),A4:D7,MATCH(E1,A4:D4,0),FALSE))

But I don't think it works because I can't seem to get the vlookup function to return an array. I'm also not sure if it did return an array it would work with the sumproduct.

Can vlookup return an array? If not, is there some other function I should be using?

I'm also new to this forum so let me know if I've not framed this question properly.

Please and Thank You.

Hi, welcome to the forum

So you are looking for something like...
Bx Ta = 40 (20*2)
Bx Tb = 75 (25*3)
?

Try this as in E2 and fill across to G2 as below.

Formula:

Please Login or Register  to view this content.

or alternatively this formula

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1		Unit Type A	Unit Type B	Unit Type C	Building X	Building Y	Building Z
2	Bedrooms	2	3	5	95	235	310
3
4	# Unit types per Bldg	Building X	Building Y	Building Z
5	Unit Type A	20	30	40
6	Unit Type B	10	25	60
7	Unit Type C	5	20	10

An afterthought.

Lay out the individual bedroom counts in E5:G7 with

Formula:

Please Login or Register  to view this content.

and then in E2 filled across G2

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1		Unit Type A	Unit Type B	Unit Type C	Building X	Building Y	Building Z
2	Bedrooms	2	3	5	95	235	310
3
4	# Unit types per Bldg	Building X	Building Y	Building Z
5	Unit Type A	20	30	40	40	60	80
6	Unit Type B	10	25	60	30	75	180
7	Unit Type C	5	20	10	25	100	50