Using the same data as before with the first name of List 1 on Sheet1 column A Row 3
and the first name of List 2 on Sheet2 column A Row 3
Put this formula in Cell A3 on Sheet3
- change $A$14 as required.
Drag the formula down to extend it as before.
You will get the same results.
On sheet3 to get the 'additional data' use a further VLOOKUP()
In cell B3 put this:
- change $J$28 as required.
This first tests to see if there is an unmatched name in column A.
If there is, you know it must appear in column A of Sheet1, so we can use a simple VLOOKUP() to return data from columns to the right of the name.
VLOOKUP() returns data from a column to the right of the found item. As we want to use a series of consecutive columns, I have used COLUMN() in place of the number - it saves typing in 2, 3, 4 etc. COLUMN() will have the value 2 in column B and 10 in column J
As usual note the $ signs, to allow this formula in B3 to be dragged right, out to say cell J3 and then drag B3 to J3 down 8000 or so rows.