Author: Oscar Cronquist Article last updated on February 01, 2019

The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based on a cross-reference schedule.

The schedule has dates in row 2, however, the cell is formatted to show only the day number.

Names are in column A and there are no duplicate names. An X indicates that a date is selected for the given person, multiple x's in a sequence is a date range.

The array formula in cell B3 returns the correct number names needed to return all date ranges:

=IFNA(INDEX(Sheet1!$A$3:$A$7, MATCH(FALSE, COUNTIF($B$2:B2, Sheet1!$A$3:$A$7)=(MMULT((Sheet1!$B$3:$AA$7<>Sheet1!$C$3:$AB$7)*1, TRANSPOSE(COLUMN(Sheet1!$B$3:$AA$4)^0))/2), 0)), "")

Array formula in cell C3:

=SMALL(IF((Sheet1!$B$3:$NB$7<>Sheet1!$C$3:$NC$7)*($B3=Sheet1!$A$3:$A$7), Sheet1!$B$2:$NB$2+1, ""), COUNTIF($B$3:B3, B3)*2-1)

Array formula in cell D3:

=SMALL(IF((Sheet1!$B$3:$NB$7<>Sheet1!$C$3:$NC$7)*($B3=Sheet1!$A$3:$A$7), Sheet1!$B$2:$NB$2, ""), COUNTIF($B$3:B3, B3)*2)
  1. Sheet1!$A$3:$A$7 is the cell reference to the names in the schedule.
  2. Sheet1!$B$3:$AA$7 and Sheet1!$C$3:$AB$7 are a cell reference to the X's in the schedule.
    Two are needed for the formula to count the number of date ranges.
    Note! The cell refs have the same size, however, the latter is offset by one column.

To enter an array formula, type the formula in cell B3 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

Explaining the formula in cell B3

Use "Evaluate Formula" on tab "Formula" on the ribbon to go through the steps.

Step 1 - Count previous values

COUNTIF($B$2:B2,Sheet1!$A$3:$A$7)

becomes

COUNTIF("Name", {"Graham Chapman"; "John Cleese"; "Eric Idle"; "Terry Gilliam"; "Michael Palin"})

and returns {0;0;0;0;0}. None of the names have been displayed in cells above, remember that I am right now showing the calculation steps in cell B3.