I have one file where i have 10000 names. In another file i have some names
(for example 150) that have a number associated. I want this numbers appears
in the file that have 10000 names

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.


"rodrigo" wrote:

I have one file where i have 10000 names. In another file i have some names
(for example 150) that have a number associated. I want this numbers appears
in the file that have 10000 names

Oops! I realized just as soon as I posted that I'd screwed up the IF(ISNA())
formula by including extra = symbols. That darned cut'n'paste will get you
every time!
Here's what it really should look like:
=IF(ISNA(VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$150,4,FALSE)),"",VLOOKUP(A2,[Book2]Sheet1!$A$1:$D$150,4,FALSE))



"rodrigo" wrote:

I have one file where i have 10000 names. In another file i have some names
(for example 150) that have a number associated. I want this numbers appears
in the file that have 10000 names