Forum Discussion

Dakkshinamurthi Pan Subramanian's avatar
Dakkshinamurthi Pan Subramanian
Copper Contributor
Aug 31, 2017
Solved

Array formula not working in my computer

Hallo People,   I want to extract values of a row which has duplicates into seperate columns. I used the below formula {=https://exceljet.net/excel-functions/excel-iferror-function(https://exce...
excel
Excel Online
Formulas and Functions
  • Dakkshinamurthi Pan Subramanian's avatar
    Dakkshinamurthi Pan Subramanian
    Sep 01, 2017

    Hi, 

     

    It is working !! The syntax was wrong.

    =IFERROR(INDEX(names;SMALL(IF(groups=$E5; ROW(names)-MIN(ROW(names))+1);COLUMNS($E$5:E5)));"")

     

    This worked. The snytax on this excel was seperated by semi colons and not commas. 

    Thanks and best regards,

     

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 31, 2017

Hi,

 

No problems with this formula on PC as well if you enter it correctly (properly defined name ranges and enter as array formula).

Copy/paste this one

=IFERROR(INDEX(Names,SMALL(IF(groups=$E5,ROW(Names)-MIN(ROW(Names))+1),COLUMNS(D$5:$E5))),"")

and enter with Cntrl+Shift+Enter

  • california2007's avatar
    california2007
    Copper Contributor
    Apr 03, 2022

    SergeiBaklan 

    Hi,

    I am running into an array formula problem using MS 356 Excel (under a similar setting as this one). The formular is:

    {=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$1:E5))),"")}

     

    The problem is the “ROWS($E$5:E5))” does not work properly under array formula setting:

    Instead of a number sequence: 1, 2, 3, 4 .., the array formula resulted in a single number: 1. I also tried different computers and “ROW()” formula. They showed same results.

    I would appreciate any helps.  Thank you in advance!

     

    Chuck

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 03, 2022

      california2007 

      365 works with arrays natively, on practice you may forget about old style array formulas.

       

      However, if you Ctrl+Shift+Enter formula in 365 it returns only first elements of the array. For example, {=SEQUENCE(4)} returns single element 1.

      • california2007's avatar
        california2007
        Copper Contributor
        Apr 04, 2022
        Thank you for your help. You are right that 365 works with arrays natively! However, I do solve my problem by using "aggregate" function after looking this video:
        https://www.bing.com/videos/search?q=excel+rows+does+not+work+in+arry+function&docid=608008378289452945&mid=225508992978832E475F225508992978832E475F&view=detail&FORM=VIRE&msclkid=eeca77e3b43211ecac1e1d8a6f8b7e87
        For those who have the similar issues, it is good choice!

        Chuck
  • Dakkshinamurthi Pan Subramanian's avatar
    Dakkshinamurthi Pan Subramanian
    Copper Contributor
    Sep 01, 2017

    Hi,

    Thank you for the fast response.

    I did it but i faced the same error message. I tried selecting the whole column and only the data set as Named range and both didnt work. 

    COLUMNS(D$5:$E5)

    Is it supposed to be D or E. Both did not work anyway. 

    FYI, I am using an English version of Excel on a German language system (German keyboard settings). 

    Best regards,

    • Dakkshinamurthi Pan Subramanian's avatar
      Dakkshinamurthi Pan Subramanian
      Copper Contributor
      Sep 01, 2017

      Hi, 

       

      It is working !! The syntax was wrong.

      =IFERROR(INDEX(names;SMALL(IF(groups=$E5; ROW(names)-MIN(ROW(names))+1);COLUMNS($E$5:E5)));"")

       

      This worked. The snytax on this excel was seperated by semi colons and not commas. 

      Thanks and best regards,

       

Resources