Hi!

Here's one way:

A51 = date criteria

Array entered:

=INDEX(A$2:A$50,SMALL(IF(INDEX(B$2:CA$50,,MATCH(A$ 51,B$1:CA$1,0))="a",ROW(A$2:A$50)-ROW(A$2)+1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

We can suppress the display of the #NUM! errors using an error trap in the
formula. This would make the formula twice as long and add to its
complexity. An alternative method is to use conditional formatting to hide
the errors.

Select the cells that hold the formula
Goto FormatConditional Formatting
Formula is: =(ISERROR(cell_ref)
Click the Format button
Set the font color to be the same as the background color
OK out

Biff

"CJ-22" wrote in
message ...

Hi,

I am new to array formulas, but so far I am thoroughly impressed with
what they can do. I am stuck right now on one and was hoping for some
help. I am a teacher and I keep daily records of my students on excel.
My daily record spreadsheet is similar to this:

Row 1 - Dates for each school day
Column A - Student Names
B2:CA50 - Daily Record keeping such as absence, tardy, homework, etc.

What I want is a column that will list all absences for a specific day.
In other words, have a cell where I can input any date and a list would
generate for all the students absent on that date. The formula would
need to search row 1 for the date, find all "A"s in the column of that
date, and return the name in column A. Can this be done?

Thanks for any replies.

CJ-22


--
CJ-22
------------------------------------------------------------------------
CJ-22's Profile:
http://www.excelforum.com/member.php...o&userid=17551
View this thread: http://www.excelforum.com/showthread...hreadid=506632