Array formula, greater than error

Hi everyone,

I have an issue with the array formula in AIC:AIC

The formula is meant to look at AIB:AIB, & select the max value between AIB2 & AIC2

The formula works correctly when a number in AIB:AIB is < AIC2

But incorrectly selects numbers > AIB2

Row 47 is where the error occurs, the answer should be 0 as it doesn't fall between 250 & 200

I imagine it should be pretty straight forward fix

Ty for reading

Mdn

Try removing the N() wrapper from your formula, i.e.,

=MAX(IF($A$3:$A$154=A3,IF($AIB$3:$AIB$154>=$AIC$2,IF($AIB$3:$AIB$154<=$AIB$2,$AIB$3:$AIB$154))))

Cheers Root, legend!

Oh very nice Flame

One N(+ would be enough

=MAX(INDEX(($A$3:$A$154=A3)*($AIB$3:$AIB$154>=$AIC$2)*($AIB$3:$AIB$154<=$AIB$2)*N(+$AIB$3:$AIB$154),0))