To put this in context, I'm working on my fantasy football spreadsheets. I
have various pages in a workbook that have similar data. So, for example,
sheet one is from XYZ Fantasy Rankings and has the following data:

[1] [QB] [Peyton Manning]
[2] [QB] [Daunte Culpepper]
[3] [QB] [Donovan McNabb]
[4] [QB] [Trent Green]
[5] [QB] [Kerry Collins]
Sheet two contains the following:

[1] [QB] [Peyton Manning]
[2] [QB] [Donovan McNabb]
[3] [QB] [Trent Green]
[4] [QB] [Daunte Culpepper]
[5] [QB] [Mark Bulger]

(Sheet 2 is obviously fake!)

The brackets mean the data are already set out in different cells, not that
they appear in the spreadsheet.

I would like to create a third sheet that parses the first two and gives me
the following result:

[QB] [Peyton Manning] [1] [1]
[QB] [Daunte Culpepper] [2] [4]
[QB] [Donovan McNabb] [3] [2]
[QB] [Trent Green] [4] [3]
[QB] [Kerry Collins] [5] [6]
[QB] [Mark Bulger] [6] [5]

How should I go about setting up this third sheet? And let's assume that
I've used CONCATENATE and TRIM to get the names to all be in the same format
on pages 2 and 3 (or, more likely, that I'll go in and fix them by hand so
they are!)

Also, sheets 1 and 2 might be of different lengths and there might be names
that appear on one sheet but not the other. As a bonus question, for those
names that don't appear on a list, I would like to force a value to return
that is one greater than the highest number returned (so, for example, Bulger
would be treated as if he had gotten a 6 ranking on sheet 1, even though he
doesn't appear there).

I've been stumbling through the INDEX and LOOKUP help files but just haven't
had any luck.

Thanks very much in advance