Forum Discussion

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

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

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
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,