Forum Discussion

None None's avatar
None None
Copper Contributor
Feb 08, 2018

Returning a text value from an array

I have an array.  and want to search a specific column for a value greater than 0 in the array.  if i find a value greater than 0 i want to return the text entered in a different column of the array....
Formulas & Functions
tips and tricks
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 08, 2018

Hi,

 

It could be like

=IFNA(INDEX(<category column>, MATCH(<number>,<label column>,0)),"")
None None's avatar
None None
Copper Contributor
Feb 08, 2018

Thank you 

That did not work for what I wanted it to do

  • Willy Lau's avatar
    Willy Lau
    Iron Contributor
    Feb 08, 2018

    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).

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 08, 2018

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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Feb 09, 2018

        Hi,

         

        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) >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).

         

        How the formula works is here 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.

         

        Please see attached.

Resources