Find Last Occurence
 

Worksheet with individuals down and dates across. Attendance is marked with
"x". Need to find last time individual was at work (last x) and return date
in first column.

Find Last Occurence
 

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is marked with
"x". Need to find last time individual was at work (last x) and return date
in first column.

Find Last Occurence
 

Thanks for the reply. Being a new user of Excel I couldn't get the formula to
work. What would the formula look like if the date array goes from AC7:BK7
the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in
BK8. I need the date of their attendance (corresponding to the last x in
row). There are several x's scattered in the row. I plan to copy the formula
down to get an answer for each student. I tried the formula wizard and it
gave a different formula that returned a date but it was not the last
attendance.

"Teethless mama" wrote:

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is marked with
"x". Need to find last time individual was at work (last x) and return date
in first column.

Find Last Occurence
 

Opps - Result would be displayed in BL9

"bill78759" wrote:

Thanks for the reply. Being a new user of Excel I couldn't get the formula to
work. What would the formula look like if the date array goes from AC7:BK7
the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in
BK8. I need the date of their attendance (corresponding to the last x in
row). There are several x's scattered in the row. I plan to copy the formula
down to get an answer for each student. I tried the formula wizard and it
gave a different formula that returned a date but it was not the last
attendance.

"Teethless mama" wrote:

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is marked with
"x". Need to find last time individual was at work (last x) and return date
in first column.

Find Last Occurence
 

Assuming the range contains only the TEXT entry X, otherwise the cells are
empty.

Try this:

=IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"")

Format as Date

--
Biff
Microsoft Excel MVP


"bill78759" wrote in message
...
Opps - Result would be displayed in BL9

"bill78759" wrote:

Thanks for the reply. Being a new user of Excel I couldn't get the
formula to
work. What would the formula look like if the date array goes from
AC7:BK7
the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in
BK8. I need the date of their attendance (corresponding to the last x in
row). There are several x's scattered in the row. I plan to copy the
formula
down to get an answer for each student. I tried the formula wizard and it
gave a different formula that returned a date but it was not the last
attendance.

"Teethless mama" wrote:

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is
marked with
"x". Need to find last time individual was at work (last x) and
return date
in first column.

Find Last Occurence
 

Biff,

Thanks, I was able to get the equation to work. I did have to put a $ sign
to lock the range of the dates. I appreciate your help.

Bill

PS- I did press the yes that it was helpful.


"T. Valko" wrote:

Assuming the range contains only the TEXT entry X, otherwise the cells are
empty.

Try this:

=IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"")

Format as Date

--
Biff
Microsoft Excel MVP


"bill78759" wrote in message
...
Opps - Result would be displayed in BL9

"bill78759" wrote:

Thanks for the reply. Being a new user of Excel I couldn't get the
formula to
work. What would the formula look like if the date array goes from
AC7:BK7
the first row is Row 9 (ie: AC9:BK9) and the result would be displayed in
BK8. I need the date of their attendance (corresponding to the last x in
row). There are several x's scattered in the row. I plan to copy the
formula
down to get an answer for each student. I tried the formula wizard and it
gave a different formula that returned a date but it was not the last
attendance.

"Teethless mama" wrote:

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is
marked with
"x". Need to find last time individual was at work (last x) and
return date
in first column.

Find Last Occurence
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"bill78759" wrote in message
...
Biff,

Thanks, I was able to get the equation to work. I did have to put a $ sign
to lock the range of the dates. I appreciate your help.

Bill

PS- I did press the yes that it was helpful.


"T. Valko" wrote:

Assuming the range contains only the TEXT entry X, otherwise the cells
are
empty.

Try this:

=IF(COUNTIF(AC9:BK9,"x"),INDEX(AC7:BK7,MATCH("zzzz z",AC9:BK9)),"")

Format as Date

--
Biff
Microsoft Excel MVP


"bill78759" wrote in message
...
Opps - Result would be displayed in BL9

"bill78759" wrote:

Thanks for the reply. Being a new user of Excel I couldn't get the
formula to
work. What would the formula look like if the date array goes from
AC7:BK7
the first row is Row 9 (ie: AC9:BK9) and the result would be displayed
in
BK8. I need the date of their attendance (corresponding to the last x
in
row). There are several x's scattered in the row. I plan to copy the
formula
down to get an answer for each student. I tried the formula wizard and
it
gave a different formula that returned a date but it was not the last
attendance.

"Teethless mama" wrote:

=LOOKUP(2,1/(2:2="x"),1:1)


"bill78759" wrote:

Worksheet with individuals down and dates across. Attendance is
marked with
"x". Need to find last time individual was at work (last x) and
return date
in first column.