Have you done as I suggested back at Post 4. Go to FORMULAS/FORMULA AUDITING/EVALUATE FORMULA. There you can see EXACTLY what the formula does, step-by-step. It could take hours to type out a full and detailed explanation. You must be prepared to help yourself!!!

In the case of your specific question:
(Sheet1!$A$1:$A$24=$A1) This means that Excel looks through all the values between A1 and A21 in sheet 1 for a match with A1 on the sheet in which the formula is located. If it finds a a match it returns TRUE, if not it returns FALSE. So the array is a total of 21 values, either TRUE or FALSE.