Need to return date (or column referance) of last criteria match on Attendance Workbook

Assuming the S stands for Sick...
Try this in F6 and filled down

=LOOKUP(2,1/($G6:$CR6="S"),$G$5:$CR$5)

Bryan,

I can't thank you enough! You have solved in minutes what has taken me days to work on. I guess knowledge is everything. Before I mark this post as solved, would you be so kind as to walk me through the first part of the lookup function? I can follow most of it, but the 2,1/ has me a bit stumped. What does it refer to and why does it work. I guess asking questions is the only way to learn.

Thanks again,

Scott

Glad to help Las Vegas

This creates an array of Trues and Falses..
($G6:$CR6="S")
Is G6=S, Is H6=S, Is I6=S,etc
So you get something like
=LOOKUP(2,1/{True,False,False,True,etc},$G$5:$CR$5)
In Excel, True = 1 and False = 0
=LOOKUP(2,1/{1,0,0,1,etc},$G$5:$CR$5)

Then the 1/ does the devision operation on each 1/0
1/1 = 1
1/0 = #VALUE Error

so now you have
=LOOKUP(2,{1,#VALUE,#VALUE,1,etc},$G$5:$CR$5)

Fortunately LOOKUP ignores error values, so it only Sees the 1's.
Since it's looking for 2 among an Unsorted array with no numbers larger than 1
It then finds the last numerical value (non error) to the right.
Then returns the corresponding value from $G$5:$CR$5

Hope that helps.

Bryan,

You are truly an Excel GOD amongst men! Thank you for assisting and taking the time to allow me to understand what the function is doing. It's people like you that make these forums a success. I will now take my new found knowledge and implement.

Thank you again,

Scott

Las Vegas,

You're welcome