Finding Last "P"

[Bundy]

How can you find the last "P" on a line, without an Array formula or Code? (Excel 2000)

JA Regional Rosters work in progress.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Names	Hours	8:30AM	8:45AM	9:00AM	9:15AM	9:30AM	9:45AM	10:00AM	10:15AM	10:30AM	10:45AM	11:00AM	11:15AM	11:30AM	11:45AM	12:00PM	12:15PM	12:30PM	12:45PM	1:00PM	1:15PM	1:30PM	1:45PM	2:00PM
2	Tom	900-530			P	P	P	P	P	L	L	P	P	B1	P	P	C	C	P	P	P	P	C	C	C
3	****	0								p	p	p	p	p	L	L	L	c	c	c			B1	c	c
4	Harry	900-500								p	p	p	B1					L	L	L				c	c
5	Fred	900-500
WEEK 1

 

[dave3009]

Try LOOKUP

=LOOKUP("p",4:4, COLUMN(4:4))

Will return the last instance of P in a given row in this case row 4

 

[barry houdini]

What do you want to return, the time from row 1? Try this formula for row 2 copied down

=LOOKUP(2,1/(C2:Y2="P"),C$1:Y$1)

format as time

 

[Bundy]

What do you want to return, the time from row 1? Try this formula for row 2 copied down

=LOOKUP(2,1/(C2:Y2="P"),C$1:Y$1)

format as time

Thanks! I knew there was a way!

How does this work though?

LOOKUP(lookup_value,array)
or
LOOKUP(lookup_value,lookup_vector,result_vector)

2 isn't the lookup value we are after?

 

[schielrn]

Lookup will return the last value of a match. If there is no match and the lookup value is greater than anything in the list it will move to the next closest value in the list. In this case it doesn't necessarily need to be 2, it could be Lookup(1.01,lookup_vector,result_vector) It just needs to be greater than 1 because (C2:Y2)="P" is going to return true/false which equates to a 1 (True) or 0 (False). So it would be either 1/1 or 1/0 which the latter would form a Div/0 error and the first one would result in 1. Then lookup just returns the last 1 in the range.

It could probably be explained a lot better, but hope that helps.

 

[Bundy]

Thanks All!