Formula Explanation

Question

What does this excel formula mean. =IFERROR(INDEX(Appointments!$A$1:$A$1200,SMALL(IF(Appointments!$D$1:$D$1200="COL",ROW($D$1:$D$1199)),ROW(57:57))),"")

Answer 1

The formula

=IFERROR(
    INDEX(Appointments!$A$1:$A$1200,
          SMALL(
              IF(Appointments!$D$1:$D$1200="COL", ROW($D$1:$D$1199)),
              ROW(57:57)
          )
    ),
    ""
)

can be read in logical steps:

1. Appointments!$D$1:$D$1200="COL"
   • Creates an array of TRUE/FALSE values indicating which rows in column D of the Appointments sheet contain the text "COL".
2. IF(Appointments!$D$1:$D$1200="COL", ROW($D$1:$D$1199))
   • For each row where column D = "COL", returns that row’s row number.
   • For rows where column D is not "COL", returns FALSE (or nothing, depending on context).
   • Result: an array of row numbers for all rows where D = "COL".
3. ROW(57:57)
   • Returns the number 57.
   • Used here as the k‑th position for SMALL, so this formula is asking for the 57th smallest row number from the array of matching rows.
4. SMALL( … , ROW(57:57))
   • From the list of row numbers where D = "COL", returns the 57th smallest row number.
   • In effect: “give the row number of the 57th appointment where column D = "COL"”.
5. INDEX(Appointments!$A$1:$A$1200, <that row number>)
   • Uses that row number as the position in the range Appointments!$A$1:$A$1200.
   • Returns the value from column A on the same row as the 57th "COL" in column D.
6. IFERROR( … , "")
   • If any error occurs (for example, there are fewer than 57 rows where D = "COL"), returns an empty string "" instead of an error like #N/A or #REF!.

Overall meaning:

The formula returns the value from column A on the Appointments sheet corresponding to the 57th row where column D contains "COL". If there are not that many matches (or another error occurs), it returns a blank cell instead of an error.

---

References:

• IFERROR function
• INDEX function
• How to correct a #N/A error in INDEX/MATCH functions
• Guidelines and examples of array formulas
• Detect formula errors in Excel