=IFERROR(INDEX('Raw Announcements'!$C$1:$C$100, SMALL(IF(INDEX('Raw Announcements'!$A$1:$A$100&'Raw Announcements'!$B$1:$B$100=$A$1, 0), ROW('Raw Announcements'!$C$1:$C$100), ""), ROW(A1))), "")


The INDEX is making a single reference list of the values in A1:A100 & B1:B100 on the Raw sheet. So in the example sheet, since A3=Claims and B3=Manager, that creates a string ClaimsManager. A4=Claims and B4=(blank) so the string created is just Claims. And so on. The array strings those 100 pairs of cells together.

The IF() function that is wrapped around the index above tests each of those strings created above to see if the string created matches the value in cell A1 on that sheet. So on the Claims sheet, it would make a match on rows 4, 12, and 14-100, all are just "claims", so they would all endup in the array. {4,12,14,15,16,17,etc....}

The SMALL() function that is wrapped around that is used to grab one value at a time out of that array. SMALL(array, k). For "k" we start with ROW(A1) which translates to 1, so SMALL takes the first value in the array, in this case 4.

The final step is the outer INDEX() function. We made an index of the entire column C on the Raw sheet, now we want the value in the "row" that matches the value the SMALL() returned. The first value it found was 4 (row 4), so it returns the 4th value in column C... which is the message "Never Mind". So that is the FIRST result you get in B3 with that initial formula.

Now, when B3 is copied DOWN to B4, all the parts of the formula stay the same except the final ROW(A1) changes to ROW(A2). Row(A2) translates to 2, so now the SMALL() array will spit out the second smallest number, in this case 12. The 12th value in column C of RAW is "Message10".

Now you see how it works.
============================

The second section of array formulas starts again at B11 on the Claims sheet and the formula is almost identical, except we add the word "Manager" to the value in A1 to create "ClaimsManager", and the inner INDEX is now creating an array of rows that resolve to that instead of just "Claims". Other than that, all is the same.