Pulling specific info from one worksheet to another

[export9]

I have two worksheets.
One is old, one is new.
The new sheet needs the same info pulled in as the old sheet, but in a different order, by last name.

There are two columns of info associated with the last name, each will need pulled in separately. I have tried a couple different formula with no luck. Any ideas?

 

[Toadstool]

Here's one way...

Here's my sheet "Old" with columns A to C in rows 1 to 18 containing my data:

First Name	Surname	Corporation
Susan	Smith	ACME Widgets
Bert	Jones	Altwood Bits
Alf	Carruthers	Bridges Stuff
Sarah	Pollard	Blacks Things
Zoe	Green	Carter PLC
Vanessa	Attwood	Dry & Co.
Hannah	Ross	Easy Partners
John	Connelly	Green & Son
Lily	Smithers	Hope & Sky Inc.
Keith	Johnson	India Trading
Bob	Green	Carter PLC
Stella	Mitchell	Krill Corp.
John	Tripp	Lily Interiors
John	Jackson	Mint Trading
Charlie	White	White Sky Inc.
Nigel	Black	Whites
Zoe	Zoomer	Zoe & Partners

<colgroup><col><col><col></colgroup><tbody>
</tbody>

On my "New" sheet I first want to figure out what the sequence (Seq.) should be alphabetically but also allowing for duplicate surnames. In E2 I enter
=COUNTIF(Old!$B$2:$B$18,"<"&Old!B2)+COUNTIF(Old!B$2:B2,Old!B2)
...and copy it down as many rows as I have data.

Now I can just INDEX into the columns I want to retrieve using the ROW()-1 (so I'm starting the formula in row 2 but want to retrieve the 1st entry) by matching the sequence number:
=INDEX(Old!$B$2:$B$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the Surname
=INDEX(Old!$A$2:$A$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the First Name and
=INDEX(Old!$C$2:$C$18,MATCH(ROW()-1,$E$2:$E$18,0)) gets me the Corporation

Surname	First Name	Corporation		Seq.
Attwood	Vanessa	Dry & Co.		13
Black	Nigel	Whites		9
Carruthers	Alf	Bridges Stuff		3
Connelly	John	Green & Son		11
Green	Zoe	Carter PLC		5
Green	Bob	Carter PLC		1
Jackson	John	Mint Trading		12
Johnson	Keith	India Trading		4
Jones	Bert	Altwood Bits		14
Mitchell	Stella	Krill Corp.		8
Pollard	Sarah	Blacks Things		6
Ross	Hannah	Easy Partners		10
Smith	Susan	ACME Widgets		15
Smithers	Lily	Hope & Sky Inc.		7
Tripp	John	Lily Interiors		16
White	Charlie	White Sky Inc.		2
Zoomer	Zoe	Zoe & Partners		17

<colgroup><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>