How can I copy desired cell content ?

I'm using Excel 2007 and s/s is 325501 rows deep. It consists of series of ranges between 4 and 30 rows deep.

What I want to do is locate the next appearance of a name and copy its accompanying number.
Doing this manully is not feasible, given the large size of the s/s .

I enclose a small attachment showing what I am trying to achieve. For those who don't like opening attachments the wording in it is :

The desired objective is to place in column Q the next appearing number in column L of the name in column C.
The VLOOKUP formula in column Q presents the desired number but (problem!) presents a zero when next appearance = blank.
When this happens I want the formula/code to repeatedly lookup the next appearance until it finds a number.
Examples of where next numbers appear are given here in column R.


If anyone can help me to this end I would be most grateful.
Big thanks.
Colwyn.