japorms,

Enter this in cell A2:
=IF(COUNT($A$1:A1)<15,IF(ISNUMBER(D2),D2,""),"")
And a formula like this in cell B2 (this assumes your numbers are in column A and the names in
column B - you may need to change the Vlookup range or change the vlookup to a combo if index and
match, depending on your layout of sheet1):
=IF(A2<"",VLOOKUP(A2,Sheet1!A:B,2,FALSE),"")

Copy them both down column A to match your data in column D.

HTH,
Bernie
MS Excel MVP


"japorms" wrote in message
...

Requirements:

First it needs to search in column D for a numeric number (personnel
number) and look it up in a list from sheet 1 (contains name and
personnel number) and once it find a matching personnel number. It
will copy and paste the personnel number in column A while the name
will be paste in column B. When it encounters a blank cell it will
jump to another row in column D and look for a personnel number again.
Once it jump for the 15th time and not yet find a personnel number, it
will now stop.


--
japorms
------------------------------------------------------------------------
japorms's Profile: http://www.excelforum.com/member.php...fo&userid=6544
View this thread: http://www.excelforum.com/showthread...hreadid=468141