I have several names in a list that do not exactly match information on another sheet. How would I go about changing my VLookup to accommodate these differences in the names? I have managed to make it work for most, but have a few that I can't manage to figure out a solution other than manually correcting the names.
Sheet 1
Sheet 2
Sheet 1
| Excel 2010 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | Bailey, Kelly R | 94.36 | ||
| 2 | Bessert, Nanette R | 93.11 | ||
| 3 | Bradley, Susan R | 92.49 | ||
| 4 | Campbell, Sharlene R | 86.16 | ||
| 5 | Del Real, Paul | 83.6 | ||
| 6 | Garcia, Sally R | 90.91 | ||
| 7 | Jackson, Sarah H | 93.14 | ||
| 8 | Jarvis, Brenda J | 88.07 | ||
| 9 | Lindo, Mary M | 88.52 | ||
| 10 | Lippert, Jacqueline R | 92.04 | ||
| 11 | Lizarraras, Virginia R | 93 | ||
| 12 | Marine Dominguez, Deirdre | 87.86 | ||
| 13 | McClinton, Bradley W | 78.88 | ||
| 14 | Pollard, Pamela R | 96.13 | ||
| 15 | Ragland, Letitia R | 91.07 | ||
| 16 | Schwenk, Kathy R | 86.83 | ||
| 17 | Strellrecht, Lisa | 92.92 | ||
| 18 | Sullivan, Kathleen J. | 93.47 | ||
| 19 | Vail, Sharleen R | 95.67 | ||
| 20 | Wright, Cheryl L | 80.81 | ||
Sheet1 | ||||
Sheet 2
| Excel 2010 | ||||
|---|---|---|---|---|
| A | B | |||
| 1 | BAILEY, KELLY | #N/A | ||
| 2 | BESSERT, NANETTE E | #N/A | ||
| 3 | BRADLEY, SUSAN C | #N/A | ||
| 4 | CAMPBELL, SHARLENE D | #N/A | ||
| 5 | DELREAL, PAUL | #N/A | ||
| 6 | GARCIA, SALLY | #N/A | ||
| 7 | JACKSON, SARAH M | #N/A | ||
| 8 | JARVIS, BRENDA, J | #N/A | ||
| 9 | LINDO, MARIA M | #N/A | ||
| 10 | LIPPERT, JACQUELINE | #N/A | ||
| 11 | LIZARRARAS, VIRGINIA | #N/A | ||
| 12 | MARINE DOMINGUEZ, DEIRDRE, M | #N/A | ||
| 13 | MCCLINTON, BRADLEY M | #N/A | ||
| 14 | POLLARD, PAMELA A | #N/A | ||
| 15 | RAGLAND, LETITIA A | #N/A | ||
| 16 | SCHWENK, KATHY J | #N/A | ||
| 17 | STELLRECHT, LISA | #N/A | ||
| 18 | SULLIVAN, KATHLEEN J | #N/A | ||
| 19 | VAIL, SHARLEEN C | #N/A | ||
| 20 | WRIGHT, CHERYL | #N/A | ||
Sheet2 | ||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B1 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A1,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A1,FIND("^",SUBSTITUTE(A1," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B2 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A2,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B3 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A3,FIND("^",SUBSTITUTE(A3," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A3,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A3,FIND("^",SUBSTITUTE(A3," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B4 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A4,FIND("^",SUBSTITUTE(A4," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A4,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A4,FIND("^",SUBSTITUTE(A4," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B5 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A5,FIND("^",SUBSTITUTE(A5," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A5,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A5,FIND("^",SUBSTITUTE(A5," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B6 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A6,FIND("^",SUBSTITUTE(A6," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A6,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A6,FIND("^",SUBSTITUTE(A6," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B7 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A7,FIND("^",SUBSTITUTE(A7," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A7,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A7,FIND("^",SUBSTITUTE(A7," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B8 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A8,FIND("^",SUBSTITUTE(A8," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A8,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A8,FIND("^",SUBSTITUTE(A8," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B9 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A9,FIND("^",SUBSTITUTE(A9," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A9,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A9,FIND("^",SUBSTITUTE(A9," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B10 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A10,FIND("^",SUBSTITUTE(A10," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A10,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A10,FIND("^",SUBSTITUTE(A10," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B11 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A11,FIND("^",SUBSTITUTE(A11," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A11,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A11,FIND("^",SUBSTITUTE(A11," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B12 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A12,FIND("^",SUBSTITUTE(A12," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A12,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A12,FIND("^",SUBSTITUTE(A12," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B13 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A13,FIND("^",SUBSTITUTE(A13," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A13,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A13,FIND("^",SUBSTITUTE(A13," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B14 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A14,FIND("^",SUBSTITUTE(A14," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A14,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A14,FIND("^",SUBSTITUTE(A14," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B15 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A15,FIND("^",SUBSTITUTE(A15," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A15,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A15,FIND("^",SUBSTITUTE(A15," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B16 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A16,FIND("^",SUBSTITUTE(A16," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A16,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A16,FIND("^",SUBSTITUTE(A16," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B17 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A17,FIND("^",SUBSTITUTE(A17," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A17,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A17,FIND("^",SUBSTITUTE(A17," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B18 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A18,FIND("^",SUBSTITUTE(A18," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A18,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A18,FIND("^",SUBSTITUTE(A18," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B19 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A19,FIND("^",SUBSTITUTE(A19," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A19,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A19,FIND("^",SUBSTITUTE(A19," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
| B20 | =IF(ISERROR(VLOOKUP(TRIM(LEFT(A20,FIND("^",SUBSTITUTE(A20," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)),VLOOKUP(A20,Sheet1!$A$1:$B$20,2,FALSE),VLOOKUP(TRIM(LEFT(A20,FIND("^",SUBSTITUTE(A20," ","^",2),1)-1)),Sheet1!$A$1:$B$20,2,FALSE)) | |
