Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
Can someone please help me with using vlookup to merge two lists based on a
common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
dlee388,
in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
Sorry, but I still can't figure it out. Is there something else that I'm
missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
dlee388,
What results are you getting? Have you copied this formula all the way down? If there is not a match in spreadsheet #2, the the formula will return an n/a error ("#N/A"). If that is what you are getting, you could modify the formula to this: =if(isna(VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)),"",V LOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE)) Doing this, the formula will make the cell APPEAR blank if there is no match and will return the results if there is a match. HTH, Conan "dlee388" wrote in message ... Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
New information: On spreadsheet #2, there appears to be a trailing space at
the end of the email address and Excel is not matching as a result of that. I copied all of the email addresses into spreadsheet #1 and then used the IF function to determine if Excel was seeing the matches on the email address. It was not seeing the matches because of that trailing space. I looked closer at the formatting on spreadsheet #2 and the cells had different alignment formatting. So I turned off wrap text and set alignment the same on both spreadsheets. I tried using the trim function to remove the trailing space, but it was not successful. I also tried using Replace to get rid of the trailing space, but the Replace function couldn't see the trailing space. However, when I click my mouse on each cell, the trailing space is definitely there and when I hit backspace to remove the trailing space, then Excel sees the email address as being a match. So perhaps now my problem has shifted to trying to figure out how to remove this trailing space before I can get vlookup to work properly. "dlee388" wrote: Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
dlee388,
The trailing space is on spreadsheet #2? (assuming your data starts in A2) In an unused column (column C for example), enter this formula in C2: =trim(A2) Copy that down to the bottom of your data. Now column C will have the data in column A, but trimmed of leading/trailing spaces. You can leave this column there and adjust your formulas to refer to this column OR you can copy column C and Paste as values (right-clickPaste Special...Values) over the top of column A, then delete column C. Then your VLOOKUP's should work without adjustments. HTH, Conan "dlee388" wrote in message ... New information: On spreadsheet #2, there appears to be a trailing space at the end of the email address and Excel is not matching as a result of that. I copied all of the email addresses into spreadsheet #1 and then used the IF function to determine if Excel was seeing the matches on the email address. It was not seeing the matches because of that trailing space. I looked closer at the formatting on spreadsheet #2 and the cells had different alignment formatting. So I turned off wrap text and set alignment the same on both spreadsheets. I tried using the trim function to remove the trailing space, but it was not successful. I also tried using Replace to get rid of the trailing space, but the Replace function couldn't see the trailing space. However, when I click my mouse on each cell, the trailing space is definitely there and when I hit backspace to remove the trailing space, then Excel sees the email address as being a match. So perhaps now my problem has shifted to trying to figure out how to remove this trailing space before I can get vlookup to work properly. "dlee388" wrote: Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
SUCCESS - FINALLY. It only took all afternoon. Turns out there was a
stubborn trailing space at the end of every email address in spreadsheet #2. I had to use the following to remove that trailing space: =TRIM(SUBSTITUTE(C20,CHAR(160)," ")) Once the trailing space was removed, I was able to get Vlookup to work based on the information that you (Conan) gave me. Thanks, Dani "dlee388" wrote: New information: On spreadsheet #2, there appears to be a trailing space at the end of the email address and Excel is not matching as a result of that. I copied all of the email addresses into spreadsheet #1 and then used the IF function to determine if Excel was seeing the matches on the email address. It was not seeing the matches because of that trailing space. I looked closer at the formatting on spreadsheet #2 and the cells had different alignment formatting. So I turned off wrap text and set alignment the same on both spreadsheets. I tried using the trim function to remove the trailing space, but it was not successful. I also tried using Replace to get rid of the trailing space, but the Replace function couldn't see the trailing space. However, when I click my mouse on each cell, the trailing space is definitely there and when I hit backspace to remove the trailing space, then Excel sees the email address as being a match. So perhaps now my problem has shifted to trying to figure out how to remove this trailing space before I can get vlookup to work properly. "dlee388" wrote: Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
dlee388,
I'm glad everything worked out. I'm not sure why =TRIM() did not work for you. It should have. Check out my response to your last post for more info/different ways of accomplishing the same thing. Conan "dlee388" wrote in message ... SUCCESS - FINALLY. It only took all afternoon. Turns out there was a stubborn trailing space at the end of every email address in spreadsheet #2. I had to use the following to remove that trailing space: =TRIM(SUBSTITUTE(C20,CHAR(160)," ")) Once the trailing space was removed, I was able to get Vlookup to work based on the information that you (Conan) gave me. Thanks, Dani "dlee388" wrote: New information: On spreadsheet #2, there appears to be a trailing space at the end of the email address and Excel is not matching as a result of that. I copied all of the email addresses into spreadsheet #1 and then used the IF function to determine if Excel was seeing the matches on the email address. It was not seeing the matches because of that trailing space. I looked closer at the formatting on spreadsheet #2 and the cells had different alignment formatting. So I turned off wrap text and set alignment the same on both spreadsheets. I tried using the trim function to remove the trailing space, but it was not successful. I also tried using Replace to get rid of the trailing space, but the Replace function couldn't see the trailing space. However, when I click my mouse on each cell, the trailing space is definitely there and when I hit backspace to remove the trailing space, then Excel sees the email address as being a match. So perhaps now my problem has shifted to trying to figure out how to remove this trailing space before I can get vlookup to work properly. "dlee388" wrote: Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with vlookup to merge two lists
Perhaps the trailing space is a non-breaking space which TRIM or CLEAN won't
remove. Try removing by EditReplace What: Alt + 0160(on numpad) With: leave blank Replace all. Gord Dibben MS Excel MVP On Mon, 3 Dec 2007 15:40:02 -0800, dlee388 wrote: New information: On spreadsheet #2, there appears to be a trailing space at the end of the email address and Excel is not matching as a result of that. I copied all of the email addresses into spreadsheet #1 and then used the IF function to determine if Excel was seeing the matches on the email address. It was not seeing the matches because of that trailing space. I looked closer at the formatting on spreadsheet #2 and the cells had different alignment formatting. So I turned off wrap text and set alignment the same on both spreadsheets. I tried using the trim function to remove the trailing space, but it was not successful. I also tried using Replace to get rid of the trailing space, but the Replace function couldn't see the trailing space. However, when I click my mouse on each cell, the trailing space is definitely there and when I hit backspace to remove the trailing space, then Excel sees the email address as being a match. So perhaps now my problem has shifted to trying to figure out how to remove this trailing space before I can get vlookup to work properly. "dlee388" wrote: Sorry, but I still can't figure it out. Is there something else that I'm missing? Here's the formula that I tried based on what you suggested: =VLOOKUP(S2,[poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260,2,FALSE) - S2 is the cell in spreadsheet # 1 that contains the email address and I want to find that exact email record in spreadsheet #2. - [poll_detail_do_you_scan_2col.xlsx]poll_detail_do_you_scan!$A$2:$B$260 is the table array referencing spreadsheet # 2 that contains the Reference information that I want to bring into spreadsheet #1. I highlighted all the data in the 2 column spreadsheet #2. - Spreadsheet #2 is a two column spreadsheet that contains email address in column #1 and the Reference information in column #2. - I removed the hyperlinks from email addresses in both spreadsheets and visually confirmed that there are no leading spaces. "Conan Kelly" wrote: dlee388, in your example, "(spreadsheet 2 table range to look for matching email address)" should be a range that is 2 columns wide by 200 + rows tall (make sure your range addresses have "$" in them in case you need to copy the formula down/over so it will refer to the same table range). In order for the vlookup function to work, you need to give the column number of the table range......not the column number of the physical column on the sheet. In your case, this will be column 2 (second column of the table range), not column 6 (physical column on the sheet). HTH, Conan Kelly "dlee388" wrote in message ... Can someone please help me with using vlookup to merge two lists based on a common email address. I want to match email addresses between the two spreadsheets and bring the data from the second spreadsheet to the matching record (email address) on the first spreadsheet. I've read all sorts of online articles and read help, but still can't understand how to get vlookup to work correctly. Spreadsheet 1 is a master list with contact information for 400 people including their email addresses in column R. Spreadsheet 2 is a second list of 200 names with their email address in column E and the Response in column F (column 6). I want to exact match records on email address and copy the Response in column F, spreadsheet 2 to the corresponding email address row in the new column on spreadsheet 1. The lists have header rows. I created a new column in Spreadsheet 1. Then in Row 2 in that new column, I created: vlookup(R2, (spreadsheet 2 table range to look for matching email address), (spreadsheet 2 column to grab the Response), False) * Vlookup is located in cell W2 * R2 in spreadsheet 1 is the cell with the corresponding email address * spreadsheet 2 column to grab the Response: I tried using 6 for the column F in spreadsheet two where Response is located. I also tried highlighting the entire column 6 in spreadsheet 2. * False for exact match I can't get vlookup to work properly. I hope that my explanation is clear. I would greatly appreciate any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vLookup from 2 different lists | Excel Worksheet Functions | |||
Merge/Combine address lists | Excel Worksheet Functions | |||
How can I merge different lists in Excel? | Excel Discussion (Misc queries) | |||
Vlookup from lists | Excel Discussion (Misc queries) | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions |