CSE ARray formula not working in the top row

[Johnny C]

I've inherited a column with formula that goes through two columns of unsorted data (from a PowerQuery) and tries to get values for them to use on SUMIFS.

The formula is :-
=IFERROR(INDEX('Consol 2'!$H$7:$H$2002,MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002)+('Consol 2'!$D$7:$D$2002<>$B6)+('Consol 2'!$E$7:$E$2002<>Supplier),0)),"")

There's a Supplier picked from a drop-down list in the cell called 'Supplier'.
Column B has a service in which picks up correctly and Consol 2 column D has that service in.
Column H has a description in which can be blank.

On the first row, if the description has a blank in, the formula above returns a 0 - which the SUMIFS can't pick up on as the SUMIFS on the description is blank.
On subsequent rows, if the description has a blank in, the formula above returns a blank - which the SUMIFS can pick up on as the SUMIFS on the description is blank.

I'm sure it's something to do with the MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002) as the value in C5 is a column header that will never appear in the list of descriptions, so just returns ar array of 0's, but subsequent rows e.g. MATCH(0,COUNTIF($C$5:$C6,'Consol 2'!$H$7:$H$2002) will be>0

Unfortunately I inherited this and don't have time for a rewrite as it's a huge model that will ultimately be maintained by the people who wrote it in the first place.

TIA

 

[RoryA]

If the relevant cells in column H are actually blank, then that formula should be returning 0 for all of them. Does it work if you append "" to the result - i.e.:

=IFERROR(INDEX('Consol 2'!$H$7:$H$2002,MATCH(0,COUNTIF($C$5:$C5,'Consol 2'!$H$7:$H$2002)+('Consol 2'!$D$7:$D$2002<>$B6)+('Consol 2'!$E$7:$E$2002<>Supplier),0))&"","")

 

[Johnny C]

Hi Rory.
Cheers but no, the whole column goes blank.

 

[RoryA]

You did array enter it?

 

[Johnny C]

Yes, selected the column, made the change, hit CSE and it kept {} round it.

 

[RoryA]

No, it needs array entering into the top cell only, then copy/fill down.

 

[Johnny C]

Cheers but no joy. I removed the array, copied it down so the C$5:C5 filled out correctly, but then when I selected it all to do CSE it copied the C$5:C5 down, probably how it's all blank. I can't fill or copy down when it's a CSE. I'm in XL2016 if that makes any odds.

 

[Johnny C]

Right fixed it. I didn't thing could CSE a single cell but you can. I only ever used them once before and that was part of a competiton.

 

[RoryA]

There are two different kinds: formulas that process arrays and return a single result (this kind), and formulas that return an array of results - those are the ones that you array enter into multiple cells at once.