Check out Excel Help for the VLOOKUP() function. You can use it even across
two files. Start by opening both files. Choose a cell on the same row with
the first name in your long list of names. Assume the names are in column A
and begin on row 2 (and your formula is going to be in column D beginning at
row 2).
Start the formula by typing:
=VLOOKUP(A2,
then select the second workbook and choose the sheet with the shorter list
of names and then select the first few cells with names over to the column
with the numbers you need. Perhaps something like cells A2 over and down to
D4 then type a comma into the formula and assuming the numbers you need are
in column D, finish the formula by typing
,4,0) and pressing the [Enter] key. Your formula will look something like
=VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$4,4,FALSE)
if you'll just change the $D$4 to $D$150 then the formula will refer to the
entire short list in the other book. The formula would then look like:
=VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$150,4,FALSE)
you can fill the formula on down for the entire long list.
Now, you're going to get a lot of #N/A! entries because a lot of names
aren't going to match. You can suppress those by wrapping the formula up in
a test for that error. A little cut and pasting makes it easy. What you'll
want to end up with is this:
=IF(ISNA(=VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$150,4,FALSE)),"",=VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$150,4,FALSE))
for your first formula. That'll keep the annoying #N/A! from showing up on
the sheet.
