Matrix Table and Array Formula

[kenderweasel]

Hi,

I've been given a set of data which has a column of tariff names on the left, and a list of included service codes across the top row. Inside this table, there are letters (A,O,N), indicating whether a particular service is included in the tariff. I need to create an array formula that looks up a given tariff, and returns the services codes that are listed as "A" for that tariff. Is this possible?

Eg,

[TABLE="width: 384"]


<colgroup><col width="64" style="width: 48pt;" span="6">
<tbody>[TR]

[TD="width: 64, bgcolor: transparent"]Tariff[/TD]

[TD="width: 64, bgcolor: transparent"]Code 1[/TD]

[TD="width: 64, bgcolor: transparent"]Code 2[/TD]

[TD="width: 64, bgcolor: transparent"]Code 3[/TD]

[TD="width: 64, bgcolor: transparent"]Code 4[/TD]

[TD="width: 64, bgcolor: transparent"]Code 5[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Tariff 1[/TD]

[TD="bgcolor: transparent"]A[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]N[/TD]

[TD="bgcolor: transparent"]A[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Tariff 2[/TD]

[TD="bgcolor: transparent"]N[/TD]

[TD="bgcolor: transparent"]N[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]O[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Tariff 3[/TD]

[TD="bgcolor: transparent"]A[/TD]

[TD="bgcolor: transparent"]A[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]A[/TD]

[TD="bgcolor: transparent"]A[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Tariff 4[/TD]

[TD="bgcolor: transparent"]N[/TD]

[TD="bgcolor: transparent"]N[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]O[/TD]

[TD="bgcolor: transparent"]A[/TD]

[/TR]


</tbody>[/TABLE]

 

[Special-K99]

With "Tariff" in A1 and "Code 1" in B1

and the given tariff in H1

in I1
=IFERROR(INDEX($B$1:$F$1,1,SMALL(IF(($B$2:$F$5="A")*(A$2:A$5=$H$1),COLUMN($B$2:$F$5)),ROW(A1))-(ROW(A$2)-1),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down to I5 (since there are only a maximum of 5 codes)

 

[kenderweasel]

Thank you so much - it works!!