Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of dates and the y is names. The table values indiciate whether or not the employee is scheduled to work (i.e., filled or not). Is there anyway to pull a list of names in a canned daily report based on the date and whether the cell is filled? In other words, I want to lookup the y-axis headers as opposed to the cross reference value.

Thanks, Geoff

Formula in cell B16:

=TODAY()

Array formula in cell B19:

=IFERROR(INDEX(\$A\$2:\$A\$14, SMALL(IF(INDEX(\$B\$2:\$AF\$14, 0, MATCH(\$B\$16, \$B\$1:\$AF\$1, 0))="x", MATCH(ROW(\$A\$2:\$A\$14), ROW(\$A\$2:\$A\$14)), ""), ROW(A1))), "")

### How to create an array formula

1. Select cell B19
2. Paste array formula in formula bar
3. Press and hold CTRL + SHIFT
4. Press Enter

### How to copy array formula

1. Select cell B19
2. Copy (Ctrl + c)
3. Select cell range B20:B25
4. Paste (Ctrl + v)

Shift scheduling.xlsx

### Conditional formatting - Cell range B2:AF14

1. Select cell range B2:AF14
2. Go to tab "Home"
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Enter this formula:=B\$1=\$B\$16
7. Click "Format.." button
8. Go to tab "Fill"
9. Pick a color
10. Click OK
11. Click OK

### Conditional formatting - Cell range A2:A14

1. Select cell range A2:A14
2. Go to tab "Home"
3. Click "Conditional formatting" button
4. Click "New Rule.."
5. Click "Use a formula to determine which cells to format"
6. Enter this formula:=INDEX(\$B\$2:\$AF\$14, ROW(A2)-1, MATCH(\$B\$16, \$B\$1:\$AF\$1, 0))="x"
7. Click "Format.." button
8. Go to tab "Fill"
9. Pick a color
10. Click OK
11. Click OK