With reference to the attached sample file, May I know what is the wrong with following an array formula, it doesn’t change the cell address automatically which highlighted with red colour.
(Most likely) That is because you selected the entire range when inputting the formula... instead, input the formula into "A3" and complete with Ctrl+Shift+Enter... then drag down and across.