INDEX with small formula issue

[JannetteChristie]

I am trying to return the information in column 2 of the following but it is not bringing back the values.

This is a worksheet called Analysis in columns x to z

CQ - Quantity	CQ - Description	CQ - Category
1	ACM panels mounted to steel box section frame	Architectural
1	Removable Transport bar	Architectural
1	Additional framework for support of heavy MVHR	Architectural
4	Removable transport feet	Architectural
1	Full width ceiling across the top of the unit	Architectural
1	Ceiling mounted PIR Operated Downlight and Series Wiring enabled	Architectural
1	Ceiling mounted Smoke Detector - Series wiring enabled	Architectural
1	Ceiling mounted Extract Valve	0
1	Ceiling provision for site fitting of Sprinkler	0
1	Soil vent pipe access hatch (Hinged)	0
1	Standard MVHR single condensate pipework incl. tundish	0
1	HIU Standard Discharge pipework incl. Teslatun	0
3	Scolmore Double Switched Socket	Electrical
2	Scolmore Single Switched Socket	Electrical
2	Scolmore 3-way 1G Minigrid Assembly - with Engraving Charge (no switches included)	Electrical

I want the records to be displayed in line 58 of the customer quote worksheet.

	Qty.		Description
58
59
60

I am using the following formula as an array :

=IFERROR(INDEX(Analysis!$X$1:$Z$1149,SMALL(IF((Analysis!$Z$1:$Z$1149="Architectural"),ROW(Analysis!$Z$1:$Z$1149)),ROW(1:1)),1),"")

Any advise greatly received, thanks

 

[Cubist]

Try:

=FILTER(Analysis!$X$1:$Z$1149,Analysis!$Z$1:$Z$1149="Architectural,"")

 

[JannetteChristie]

Thanks @Cubist for your response, however it doesn't like the formula

 

[Cubist]

Missed a quotation mark after "Architectural".

=FILTER(Analysis!$X$1:$Z$1149,Analysis!$Z$1:$Z$1149="Architectural","")

 

[JannetteChristie]

Thanks @Cubist , I should have spotted that also.
Corrected that but just getting #NA now

 

[Cubist]

It works for me.

Book3
	Y	Z	AA	AB	AC	AD
1	CQ - Description	CQ - Category		Q - Quantity	CQ - Description	CQ - Category
2	ACM panels mounted to steel box section frame	Architectural		1	ACM panels mounted to steel box section frame	Architectural
3	Removable Transport bar	Architectural		1	Removable Transport bar	Architectural
4	Additional framework for support of heavy MVHR	Architectural		1	Additional framework for support of heavy MVHR	Architectural
5	Removable transport feet	Architectural		4	Removable transport feet	Architectural
6	Full width ceiling across the top of the unit	Architectural		1	Full width ceiling across the top of the unit	Architectural
7	Ceiling mounted PIR Operated Downlight and Series Wiring enabled	Architectural		1	Ceiling mounted PIR Operated Downlight and Series Wiring enabled	Architectural
8	Ceiling mounted Smoke Detector - Series wiring enabled	Architectural		1	Ceiling mounted Smoke Detector - Series wiring enabled	Architectural
9	Ceiling mounted Extract Valve	0
10	Ceiling provision for site fitting of Sprinkler	0
11	Soil vent pipe access hatch (Hinged)	0
12	Standard MVHR single condensate pipework incl. tundish	0
13	HIU Standard Discharge pipework incl. Teslatun	0
14	Scolmore Double Switched Socket	Electrical
15	Scolmore Single Switched Socket	Electrical
16	Scolmore 3-way 1G Minigrid Assembly - with Engraving Charge (no switches included)	Electrical
Analysis

Cell Formulas
Range	Formula
AB2:AD8	AB2	=FILTER(Analysis!$X$1:$Z$1149,Analysis!$Z$1:$Z$1149="Architectural","")
Dynamic array formulas.