| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
matching values between worksheets
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.). |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
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.). |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
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. "Sean Timmons" wrote: 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.). |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
Assuming your first formula is in Sheet1!B1, enter this formula in B1:
=VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),F ALSE) 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 On Oct 29, 10:25*pm, 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.). |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
Correction:
=VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($A1:B1),F ALSE) On Oct 30, 5:37*pm, vezerid wrote: Assuming your first formula is in Sheet1!B1, enter this formula in B1: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),F ALSE) 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 On Oct 29, 10:25*pm, 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.). |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
That didn't work either... they all came back as n/a.
"vezerid" wrote: Correction: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($A1:B1),F ALSE) On Oct 30, 5:37 pm, vezerid wrote: Assuming your first formula is in Sheet1!B1, enter this formula in B1: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),F ALSE) 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 On Oct 29, 10:25 pm, 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.). |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
Did you use the first formula I suggested or the corrected formula in
the next post? On Oct 30, 5:57*pm, Ratatat wrote: That didn't work either... they all came back as n/a. "vezerid" wrote: Correction: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($A1:B1),F ALSE) On Oct 30, 5:37 wrote: Assuming your first formula is in Sheet1!B1, enter this formula in B1: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),F ALSE) 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 On Oct 29, 10:25 pm, 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.). |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
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" wrote: 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. "Sean Timmons" wrote: 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.). |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
matching values between worksheets
The corrected one.
"vezerid" wrote: Did you use the first formula I suggested or the corrected formula in the next post? On Oct 30, 5:57 pm, Ratatat wrote: That didn't work either... they all came back as n/a. "vezerid" wrote: Correction: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($A1:B1),F ALSE) On Oct 30, 5:37 wrote: Assuming your first formula is in Sheet1!B1, enter this formula in B1: =VLOOKUP($A1,Sheet2!$A$1:$A$3415,COLUMNS($B1:B1),F ALSE) 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 On Oct 29, 10:25 pm, 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.). |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching across multiple worksheets | Excel Worksheet Functions | |||
| Finding Most Recent Values in Col1 -- Summing Matching Values | Excel Discussion (Misc queries) | |||
| Matching Cells on different worksheets | Excel Discussion (Misc queries) | |||
| matching 2 worksheets together | Excel Discussion (Misc queries) | |||
| Matching and calculating 2 worksheets | Excel Worksheet Functions | |||
Linear Mode
