Another last row in column question - with a twist.

[fedude]

OK, I've read ad nauseam all the posts about finding the last row in a column
with data. I couldn't find what I need. I have a slightly different
question that I could use some help with.

Lots of columns. In the "middle" of each column are 48 rows for a test
scores. They are by date with the earliest date starting at row 14 and the
latest test date at row 61. If a person did not take the test, the column is
blank, otherwise their score is in the cell.

For each of these students, I need to find the date at which they have the
last score. So I need to search back starting at row 61 to row 14 to find the
first non-blank cell and then record the date somehow.

Is this something I can do with the existing excel formulas? If so I came
up empty. My thought was to insert a custom function in each column.

TIA!

 

[Mike H]

Maybe this

=LOOKUP(10^23,A14:A61)

Format the cell as a date

Mike

 

[fedude]

Mike,

Two other pieces of information:

1) The dates of the tests are in column B, scores start in column CZ
2) The scores may be any number from 0 to 100 and not in any sorted order.
The highest score may not be the last score.

Maybe I just don't know how the LOOKUP function works, but with only 2
parameters, it's using the array form. Won't the result be the highest score
not the last score?

 

[Don Guillett]

This is an ARRAY formula that must be entered using ctrl+shift+enter

=MAX(IF($B$2:$B$22=H1,$D$2:$D$22))
where h1 has the scorer and col b has the names and col d has the scores

 

[fedude]

Don, you are almost my hero. This formula looks simple, but..... I probably
wasn't clear in my description

I can't seem to get this to work. Probably because I don't understand how
array functions work. Partly because I can't figure out anyway MAX will work
for this problem. I believe I entered it properly because I can see the
braces.

In your formula, I don't understand what you use H1 for? The student names
are in rows 1 and 2 of each column, but I'm not looking for a name. I'm
looking for the last cell in the 14:61 range of each column that has
anything in it. I'll put this formula in row 136 of each column. This
formula will tell me the date of the last test score in the 14:61 range in
that column

The test dates are in B14:B61. Only column B has dates in them.
The test scores are in the next 200 columns in the same rows as the dates.

 

[Don Guillett]

You may send your workbook to my address below along with a snippet of this
page and I'll take a look.

 

[Don Guillett]

=IF(ISNA(MATCH(999999,C14:C61)),"",INDEX($B:$B,MATCH(999999,C14:C61)+13))

 

[fedude]

Perfect.... You are my hero!

 

[Don Guillett]

Glad to help