error in formula of match and offset

[jpbisani]

error in formula of match & offset.xlsx

hi,

in the attached file there is error which has been marked by red and the correct answer in green. I could not understand why the error when it works correctly in previous rows. thanks for your effort.

[Andrew-R]

You're using a match type of 1, which means that Excel is expecting the elements in the look-up array to be in ascending order. Because they are not it is returning unreliable results.

Change your formula in A4 to:

=INDEX(C:C,MAX(INDEX(ROW($B$2:B4)*($B$2:B4="aaa"),0)))

And copy down.

Edited to add:

Or you could use the formula:

=INDEX(C:C,MAX(INDEX(ROW($C$2:C4)*(LEFT($C$2:C4,3)="M/S"),0)))

Which would get rid of the need for column B entirely.

[jpbisani]

Dear Andrew R

Thanks for your immediate response, the problem is solved.

Regards