Index match value return 1 st cells corresponding to the range

Hi,

am trying to Countifs corresponding to the Branch the value return is 0 (highlite in yellow colour)

some cells value return is perfect that formula is {=IF(COUNTIFS($A$3:A67,A67,$C$3:C67,C67)=1,INDEX($C:$C,MATCH(1,($A:$A=$A67)*($C:$C=$C67),0)),"0")}

file attached.

pl help me.

Hi.

I'm not sure what you're trying to achieve, but using entire column references within an array formula is not at all a good idea. Each instance of the formula you give is being forced to calculate over more than two million cells.

Unlike e.g. COUNTIF(S)/SUMIF(S), array formulas calculate over all cells passed to them, whether technically beyond the last-used cells in those ranges or not.

Strongly suggest you choose a suitable upper range reference.

Regards

You omitted the use of column B

Array enter this and it will match exactly what you indicate as the desired result:

Formula:

Please Login or Register  to view this content.

@newdoverman

I thought you'd know better!

@XOR LX

Most times I don't!

Originally Posted by newdoverman

@XOR LX

Most times I don't!

__________

Thanks.

Now its working perfectly.