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" wrote:

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.).