KingPrawn 4 Posted June 13, 2012 I am admittedly an excel idiot so can somebody please help me out here. If I have a master list of names and departments and want to compare it to a secondary list how do I do that? Master list Columns A - First name Column B - Last name Column C - Department Column D - Score (currently blank) Secondary list Columns E - First name Column F - Last name Column G - Dept. Column H - Score I want to compare the names in the secondary list with the names in the master list. If the name shows up I want to fill in the score from column H automatically into column D, if not leave it blank. What is the formula to use in the cells in column D? I'm currently sorting the names, comparing the lists, and filling in column D by hand but there must be a formula for this, no? TIA Share this post Link to post Share on other sites
Patriotsfatboy1 1,432 Posted June 13, 2012 I am admittedly an excel idiot so can somebody please help me out here. If I have a master list of names and departments and want to compare it to a secondary list how do I do that? Master list Columns A - First name Column B - Last name Column C - Department Column D - Score (currently blank) Secondary list Columns E - First name Column F - Last name Column G - Dept. Column H - Score I want to compare the names in the secondary list with the names in the master list. If the name shows up I want to fill in the score from column H automatically into column D, if not leave it blank. What is the formula to use in the cells in column D? I'm currently sorting the names, comparing the lists, and filling in column D by hand but there must be a formula for this, no? TIA I think you want to lookup the function "vlookup" Share this post Link to post Share on other sites
Mrsteak21 0 Posted June 13, 2012 =VLOOKUP(B:B,B:H,7,FALSE) should be the formula in column D. Populate it all the way down column D. One caveat - if any last names are spelt the same, the scores for those people will not be correct. If that is the case you will need additional formulas - i would advise using a CONCATENATE formula, then the vlookup. Share this post Link to post Share on other sites
Patriotsfatboy1 1,432 Posted June 13, 2012 =VLOOKUP(B:B,B:H,7,FALSE) should be the formula in column D. Populate it all the way down column D. One caveat - if any last names are spelt the same, the scores for those people will not be correct. If that is the case you will need additional formulas - i would advise using a CONCATENATE formula, then the vlookup. Need to make sure that you are sorting in the same order as well. Share this post Link to post Share on other sites
KingPrawn 4 Posted June 13, 2012 =VLOOKUP(B:B,B:H,7,FALSE) should be the formula in column D. Populate it all the way down column D. One caveat - if any last names are spelt the same, the scores for those people will not be correct. If that is the case you will need additional formulas - i would advise using a CONCATENATE formula, then the vlookup. Thanks. This just seems to copy the score from column H directly into column D though regardless of name. I should note that the secondary list of names is shorter than the master list of names if that makes a difference. Share this post Link to post Share on other sites
nobody 2,044 Posted June 13, 2012 Thanks. This just seems to copy the score from column H directly into column D though regardless of name. I should note that the secondary list of names is shorter than the master list of names if that makes a difference. I think there's a slight logic error in that formula. =VLOOKUP(B:B,B:H,7,FALSE) says to look for the values in column B that match up with anything from columns B through H, and then copy the value in column H. Obviously values in column B will match the values in B, so it's just copying values in column H over. try using =VLOOKUP(B:B,F:H,3,FALSE) It'll search columns F through H for values that match with the values in B and report back what's in H. Normally I'd just search column F, but Excel wants the data it's going to copy to be included in the search table. Share this post Link to post Share on other sites
KingPrawn 4 Posted June 13, 2012 I think there's a slight logic error in that formula. =VLOOKUP(B:B,B:H,7,FALSE) says to look for the values in column B that match up with anything from columns B through H, and then copy the value in column H. Obviously values in column B will match the values in B, so it's just copying values in column H over. try using =VLOOKUP(B:B,F:H,3,FALSE) It'll search columns F through H for values that match with the values in B and report back what's in H. Normally I'd just search column F, but Excel wants the data it's going to copy to be included in the search table. Thanks Nobody. That did what I wanted to do. That compares the columns by last name. Is there a way to compare the last name and the first name or do I have to combine the names first in a new column? Share this post Link to post Share on other sites
phillybear 364 Posted June 13, 2012 Thanks Nobody. That did what I wanted to do. That compares the columns by last name. Is there a way to compare the last name and the first name or do I have to combine the names first in a new column? It would have been helpful to have a unique value to start with if available so there are no possible duplicate values, like a SSN or Employee ID, but I would add a new column and CONCATENATE the Last and First and if available Middle names for both sets of data and then do a VLOOKUP on these new values. Share this post Link to post Share on other sites
hoytdwow 202 Posted June 13, 2012 try the AUTOFELLATE feature Share this post Link to post Share on other sites
posty 2,296 Posted June 13, 2012 You can do this... In column G, concatenate the first and last name (of E and F) with... =E1&F1 Then in column D, enter... =IF(ISNA(VLOOKUP(A1&B1,G:I,3,FALSE)),"",(VLOOKUP(A1&B1,G:I,3,FALSE))) I was trying to figure out how to concatenate E and F on the fly but couldn't get it to work... Share this post Link to post Share on other sites
Mookz 1,287 Posted June 13, 2012 try the AUTOFELLATE feature That's a stretch. Share this post Link to post Share on other sites
KingPrawn 4 Posted June 13, 2012 Thanks everybody for all your help. Works beautifully. Share this post Link to post Share on other sites
