Array formula to help comparison of points

Hi all

I have data where I am comparing points, specifically I labeled points 1,2,3...and have a formula to compare point 1 to 1, 2 to 2, and so on. However, sometimes a group of numbers don't have the same number of points. For an example a trend can have 4 points and the previous trend has 3 points. In this scenario, I like to compare point 4 to point 3 (in other words, if there is no corresponding point in the previous trend, compare to the last point of the trend). I have included a screenshot and download for example. As you see I have an array formula in column G and highlighted in yellow is a point 4, but is it blank. What array formula can I use to accomplish the task I looking for?

screenshot 11216.png

in the yellow, I expect to see 6.6% which is point 3 of the previous trend. However, my formula is blank here because I was only able to program the formula to look for the same point, which in this case there is no previous point 4.

That worked JeteMc. I did modify the trend in the new attachment (sorry should have done that in the beginning) because there are scenarios like this where there can be 2 points 4s, although in this scenario I don't want it to compare to the 2.5% in yellow because that is not part of the previous trend (the number should still be 6.6% here).

thank works, I will mark thread as solved, thank you

actually found a way to use single array formula starting in g5

=IFERROR(INDEX(A:A,IF(C5>3,IF(SMALL(IF(C$5:C$31=C5,IF(C5>0,ROW(A$5:A$31))),COUNTIF(C$5:C5,C5)-1)>SMALL(IF(C$5:C$31=C5-1,IF(C5>0,ROW(A$5:A$31))),COUNTIF(C$5:C5,C5-1)-1),SMALL(IF(C$5:C$31=C5,IF(C5>0,ROW(A$5:A$31))),COUNTIF(C$5:C5,C5)-1),SMALL(IF(C$5:C$31=C5-1,IF(C5>0,ROW(A$5:A$31))),COUNTIF(C$5:C5,C5-1)-1)),SMALL(IF(C$5:C$31=C5,IF(C5>0,ROW(A$5:A$31))),COUNTIF(C$5:C5,C5)-1))),"")