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
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Apr 04, 2022

        california2007 

        Sorry, but I disagree.  To use 365 as if nothing has changed is a dreadful choice.  True Mike Girvin puts out some great material with more tricks than a cart load of monkeys, but the game has changed.  All of the time-honoured tricks with AGGREGATE (to compress a list of indices) and ROW (to generate a sequence of numbers) are no longer needed.

        = FILTER(Table, (SalesRep=SelectedRep)*(Date=selectedDate))

        is neater or, padding it out

        = LET(
    repCriterion,  (SalesRep=selectedRep)+(selectedRep=""),
    dateCriterion, (Date=selectedDate)+(selectedDate=""),
    FILTER(Table, repCriterion*dateCriterion)
  )

        is more flexible in terms of catering for omitted criteria.  Similarly, Mike's other formula would now be

        = TAKE(SORTBY(UnitsRep, Units,-1), Top)

or, failing that,

= INDEX(
    SORTBY(UnitsRep, Units,-1), 
    SEQUENCE(Top),
    {1,2}
  )

         

Resources