matching values between worksheets

[Ratatat]

I have 2 worksheets in the same workbook, one contains one column with 500
vendor names, the other contains over 3000, including those in the first
sheet, with mailing addresses. I need to say: If Sheet1A1 equals a cell in
Sheet 2, column A, copy B:F from that row into Sheet1. Ultimately, I need a
mailing list of the vendors in Sheet1. I've tried vlookup
{=VLOOKUP(A1,Sheet2!$A$1:$A$3415,Sheet2!B:F} but can't get it to work, I keep
getting errors (#Name?, #Ref!, etc.).

 

[Sean Timmons]

Rather than attempting an array, just type in 5 VLOOKUPS.

=VLOOKUP(A1,Sheet2!$A$1:$F$3415,2)

=VLOOKUP(A1,Sheet2!$A$1:$F$3415,3)

=VLOOKUP(A1,Sheet2!$A$1:$F$3415,4)

=VLOOKUP(A1,Sheet2!$A$1:$F$3415,5)

=VLOOKUP(A1,Sheet2!$A$1:$F$3415,6)

Then copy and paste to bottom.

can add a ,0 at the end of the VLOOKUP to ensure you return exact value or
#N/A if not matched exactly.

 

[Ratatat]

That worked for the first record but after that it just pulls random values
it looks like (I can't find a trend). And when I try putting in the 0 in
they all just come up n/a.

 

[vezerid]

Assuming your first formula is in Sheet1!B1, enter this formula in B1:

=VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),FALSE)

It is important to add the FALSE as 4th argument to VLOOKUP.

Now you can copy the formula down and across B:F

HTH
Kostis Vezerides

 

[vezerid]

Correction:

=VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($A1:B1),FALSE)

 

[Ratatat]

That didn't work either... they all came back as n/a.

 

[vezerid]

Did you use the first formula I suggested or the corrected formula in
the next post?

 

[Sean Timmons]

If you are getting n/a, that means the lookup value does not match exactly.
There may be a space at the end of one of the fields or something minor like
that.

without the ,0, you would end up matching against the closest match, which
can be anywhere since your table is most likely not sorted alphabetically.

I would suggest checking your vendor names against each other to see if they
match or not...

 

[Ratatat]

The corrected one.

Did you use the first formula I suggested or the corrected formula in
the next post?