LARGE( IF( AND( condition A; condition B);Array);rank)

Hi team, I am trying to sort by weight an equity index through sector and subsector.

Formula is ={LARGE(IF(AND($C:$C=$G3;$D:$D=$H3);$E:$E);1)} where in column C are sectors, column D subsectors, column E index weight.

It doesn't work though. Your help is very welcome. I attach spreadsheet sample.

Best,
XL

You can't use AND and IF like that in an array formula, since AND only returns one result. You can multiple the TRUE/FALSE (Excel substitues 1 and 0) for the following:

Formula:

Please Login or Register  to view this content.

Excellent, that works. Why do I need to enter this formula with Ctrl+Shift+Enter i.e. brackets for it to work?

Thanks Duncandhu

Originally Posted by Jambon95

Excellent, that works. Why do I need to enter this formula with Ctrl+Shift+Enter i.e. brackets for it to work?

Thanks Duncandhu

Because it is an "array formula" - these functions usually only work with a single cell, and you are specifying many.