Returning a text value from an array

Question

I have an array.&nbsp; and want to search a specific column for a value greater than 0 in the array.&nbsp; if i find a value greater than 0 i want to return the text entered in a different column of the array.&nbsp; See below for a picture.
&nbsp;
i want to search column labeled 1 and any cell with a value greater then 0 will then return the text from the cell next to in from the column titled "category"
&nbsp;
for example i search Column labeled 1 and find cell with "3" in it.&nbsp; cell with Adhesive Issues will be copied and returned to another cell.&nbsp; Also column with a 1 will return "did not cool" etc.

&nbsp;
&nbsp;
1
2
3
4
5
6
7
8

Category
Number of reports
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug

Adhesive Issues
&nbsp;
3
0
0
0
0
0
0
1

Belt Issues
&nbsp;
0
0
0
0
0
0
0
0

Broken Cradle
&nbsp;
0
0
0
0
0
0
0
0

Broken Roller Ball
&nbsp;
0
0
0
0
0
0
0
0

Broken Seal
&nbsp;
0
0
0
0
0
0
0
0

Broken Sprayer
&nbsp;
0
0
0
0
0
0
0
0

Burning Effect-Not a Claim (BeKoool &amp; OTC Only)
&nbsp;
0
0
0
0
0
0
0
0

Color Bled
&nbsp;
0
0
0
0
0
0
0
0

Contents Leaking
&nbsp;
0
0
0
0
0
0
0
0

Damaged Property
&nbsp;
0
0
0
0
0
0
0
0

Did Not Cool
&nbsp;
1
1
0
0
0
0
0
0

Difficult to Open
&nbsp;
0
0
0
0
0
0
0
0

Dried Out
&nbsp;
0
2
1
0
0
1
0
0

Ineffective (Medical Device &amp; OTC Only)
&nbsp;
0
0
0
0
0
0
0
0

Injury
&nbsp;
1
1
0
0
0
0
0
0

Low Duration
&nbsp;
0
0
0
0
0
0
0
0

sergeibaklan · Answer

Hi,
&nbsp;
When the approach approximately as Willy suggested. The formula in A43 will be
=IFERROR(
 INDEX(A$3:A$34,
   SMALL(
      IF(
         OFFSET($B$2,1,MATCH($B$42,$B$2:$M$2,0)-1,
            MATCH("Total",$A$1:$A$35,0)-MATCH("Category",$A$1:$A$35,0)-1) &gt;0,
         ROW(A$3:A$34)-ROW(A$2)
      ),
      ROWS(A$3:A3)
  )
 ),
 "")
(that is an array formula entered by Ctrl+Shift+Enter), an copy it down.
Within OFFSET first MATCH defines which column to take based on month name and the second MATCH calculates number of rows within the list (between Category and Total).
&nbsp;
How the formula works is here&nbsp;http://www.exceltactics.com/make-filtered-list-sub-arrays-excel-using-small/4/, first what I found, but there are quite a lot similar posts.
&nbsp;
Please see attached.

sergeibaklan · Answer

Hi,
&nbsp;
It could be like
=IFNA(INDEX(&lt;category column&gt;, MATCH(&lt;number&gt;,&lt;label column&gt;,0)),"")

none none · Answer

Thank you&nbsp;
That did not work for what I wanted it to do

sergeibaklan · Answer

If you attach your sample as Excel file we could give you concrete formula for that

willy lau · Answer

There is a similar case that based on a selected row header (yours is column header) to get the list of data in a row (yours is column), and then return the column headers of the data (yours is to return row header).
&nbsp;

Forum Discussion

Returning a text value from an array

6 Replies

Resources

		1	2	3	4	5	6	7	8
Category	Number of reports	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Adhesive Issues		3	0	0	0	0	0	0	1
Belt Issues		0	0	0	0	0	0	0	0
Broken Cradle		0	0	0	0	0	0	0	0
Broken Roller Ball		0	0	0	0	0	0	0	0
Broken Seal		0	0	0	0	0	0	0	0
Broken Sprayer		0	0	0	0	0	0	0	0
Burning Effect-Not a Claim (BeKoool & OTC Only)		0	0	0	0	0	0	0	0
Color Bled		0	0	0	0	0	0	0	0
Contents Leaking		0	0	0	0	0	0	0	0
Damaged Property		0	0	0	0	0	0	0	0
Did Not Cool		1	1	0	0	0	0	0	0
Difficult to Open		0	0	0	0	0	0	0	0
Dried Out		0	2	1	0	0	1	0	0
Ineffective (Medical Device & OTC Only)		0	0	0	0	0	0	0	0
Injury		1	1	0	0	0	0	0	0
Low Duration		0	0	0	0	0	0	0	0