I have a list that contains the lookup values such as "Jo" and an array that has values in single cells such as "John Doe" and a different row such as "Jo Doe" what method and settings could I use to find the cells containing "Jo Doe" and not "John Doe"? Do i need to split the array name cells to make this work, since my current XLOOKUP will return both the "Jo Doe" and the "John Doe"
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
XLOOKUP or VLOOKUP to find specific word in array cell with multiple words in it, and how?
- Thread starter xdriver
- Start date
Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,244
- Office Version
- 365
- Platform
- Windows
Are you looking for the first name Jo? You need to lookup "Jo "
Is that what you were looking for?
Is that what you were looking for?
Upvote
0
Not exactly. I have a large database and in that database there are 14,000 rows of data, where there are 2 columns of names, each cell contains a full name such as John Doe, and the next cell Jane Doe. I have a list of 1000 names I want to search for through those 14,000 rows and only in the two name columns. If a name in my name list to search for is Jo, I do not want it to return the cell that contains John Doe, but I would want it to show the row containing Jo Doe. Maybe XLOOKUP or VLOOKUP are not the right tools to use?
| 2023_11_17 - lists.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | |||
| 1 | Names | Address | City | State | Zip | Owner 1 | Owner 2 | ||||||
| 2 | Smith | Frederick H Smith | Lisa G Char-Smith | ||||||||||
| 3 | Jo | Jeffrey A Daniels | Carrie Martin | ||||||||||
| 4 | Cary | Scott A Joseph | Shelley M Joseph | ||||||||||
| 5 | Berg | Rivka Kandinov | Boris Kandinov | ||||||||||
| 6 | Nushi | Frank&s Magullion Ret | Frank Magullion Tr | ||||||||||
| 7 | Wei | Michael Escovitz | Pinky Patel | ||||||||||
| 8 | Yan | Angelo L Petulla | Takako Petulla | ||||||||||
| 9 | Li | Muhammad H Usmani | Tuba S Abbasi | ||||||||||
| 10 | Ying | Guichen Z Wong | Sanford Wong | ||||||||||
| 11 | Hui | Chris Cary | Lindsay Cary | ||||||||||
| 12 | Lei | Marc S Taylor | Anita Taylor | ||||||||||
| 13 | Hong | Darshan Dave | Swati Dave | ||||||||||
| 14 | Yu | Ferlazzo Nt | Anthony J Ferlazzo | ||||||||||
Data | |||||||||||||
Last edited:
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 66,058
- Office Version
- 365
- Platform
- Windows
Is this what you are after? If not please make up a small set of varied sample data, input the expected results manually, post that with XL2BB and add explanation specific to the samples.
| 24 01 12.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | J | K | |||||||||
| 1 | Names | Owner 1 | Owner 2 | ||||||||||
| 2 | Smith | Frederick H Smith | Frederick H Smith | Lisa G Char-Smith | |||||||||
| 3 | Jo | Jo Doe | Jeffrey A Daniels | Carrie Martin | |||||||||
| 4 | Cary | Chris Cary, Lindsay Cary | Scott A Joseph | Shelley M Joseph | |||||||||
| 5 | Berg | Rivka Kandinov | Boris Kandinov | ||||||||||
| 6 | Nushi | Frank&s Magullion Ret | Frank Magullion Tr | ||||||||||
| 7 | Wei | Michael Escovitz | Pinky Patel | ||||||||||
| 8 | Yan | Angelo L Petulla | Takako Petulla | ||||||||||
| 9 | Li | Muhammad H Usmani | Tuba S Abbasi | ||||||||||
| 10 | Ying | Guichen Z Wong | Sanford Wong | ||||||||||
| 11 | Hui | Chris Cary | Lindsay Cary | ||||||||||
| 12 | Lei | Marc S Taylor | Anita Taylor | ||||||||||
| 13 | Hong | Darshan Dave | Swati Dave | ||||||||||
| 14 | Yu | Ferlazzo Nt | Anthony J Ferlazzo | ||||||||||
| 15 | John | John Doe | John Doe | Jo Doe | |||||||||
Sheet2 (2) | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B15 | B2 | =LET(tc,TOCOL(J$2:K$15,1),TEXTJOIN(", ",1,FILTER(tc,ISNUMBER(SEARCH(" "&A2&" "," "&tc&" ")),""))) |
Upvote
0
Thank you. I should have been more specific and I am sure you can make this work if you did that. Ideally I would like to mark the row, let's say in column c that the name list appears. That way I know which row of data has one of the listed names. My list of names is about 1,000 long and my data set is about 14,000. The mark that is in column c can be a generic word like "Name List." This will enable me to sort the full array by whatever is in column c, and I can utilize the data if the row appears in the name list. Does that make sense?
Upvote
0
I have only highlighted in green and written the name from the list to make it easier to follow, but the highlight and name is not necessary. Column C would be necessary so I know that something in the Name list matches the array.
| 2023_11_17 - scrape natickmlspublicrecords.xlsx | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Names | Address | City | State | Zip | Owner 1 | Owner 2 | Last Sale Date | Last Sale Price | Type | Acre | ||||||
| 2 | Smith | Name List | Smith | Frederick H Smith | Lisa G Char-Smith | 9/28/1998 | $439,900 | 1-Family Residence - Garrison | 0.96 | ||||||||
| 3 | Jo | Jeffrey A Daniels | Carrie Martin | 9/14/2016 | $920,500 | 1-Family Residence - Contemporary | 0.96 | ||||||||||
| 4 | Cary | Scott A Joseph | Shelley M Joseph | 6/1/2004 | $549,000 | 1-Family Residence - Split-Level | 0.96 | ||||||||||
| 5 | Berg | Rivka Kandinov | Boris Kandinov | 12/1/2020 | $1,075,000 | 1-Family Residence - Colonial | 0.96 | ||||||||||
| 6 | Nushi | Frank&s Magullion Ret | Frank Magullion Tr | 11/19/2004 | $468,500 | 1-Family Residence - Colonial | 0.96 | ||||||||||
| 7 | Wei | Name list | Patel | Michael Escovitz | Pinky Patel | 6/12/2023 | $1,550,000 | 1-Family Residence - Colonial | 0.96 | ||||||||
| 8 | Yan | Angelo L Petulla | Takako Petulla | 6/1/2012 | $822,500 | 1-Family Residence - Colonial | 0.96 | ||||||||||
| 9 | Li | Muhammad H Usmani | Tuba S Abbasi | 10/1/2021 | $880,000 | 1-Family Residence - Raised-Ranch | 0.96 | ||||||||||
| 10 | Ying | Guichen Z Wong | Sanford Wong | 9/19/2008 | $480,000 | 1-Family Residence - Raised-Ranch | 0.96 | ||||||||||
| 11 | Hui | Name List | Cary | Chris Cary | Lindsay Cary | 12/16/2020 | $1,295,000 | 1-Family Residence - Old-Style | 0.96 | ||||||||
| 12 | Lei | Marc S Taylor | Anita Taylor | 2/7/2011 | $577,500 | 1-Family Residence - Split-Level | 0.96 | ||||||||||
| 13 | Hong | Darshan Dave | Swati Dave | 7/24/2009 | $507,500 | 1-Family Residence - Raised-Ranch | 0.96 | ||||||||||
| 14 | Patel | Ferlazzo Nt | Anthony J Ferlazzo | 3/1/1997 | $1 | 1-Family Residence - Raised-Ranch | 0.96 | ||||||||||
Data | |||||||||||||||||
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 66,058
- Office Version
- 365
- Platform
- Windows
Thanks for the sample and additional explanation. Try this.
| 24 01 13.xlsm | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | I | J | K | ||||||||
| 1 | Names | Owner 1 | Owner 2 | ||||||||||
| 2 | Smith | Name List | Frederick H Smith | Lisa G Char-Smith | |||||||||
| 3 | Jo | Jeffrey A Daniels | Carrie Martin | ||||||||||
| 4 | Cary | Scott A Joseph | Shelley M Joseph | ||||||||||
| 5 | Berg | Rivka Kandinov | Boris Kandinov | ||||||||||
| 6 | Nushi | Frank&s Magullion Ret | Frank Magullion Tr | ||||||||||
| 7 | Wei | Name List | Michael Escovitz | Pinky Patel | |||||||||
| 8 | Yan | Angelo L Petulla | Takako Petulla | ||||||||||
| 9 | Li | Muhammad H Usmani | Tuba S Abbasi | ||||||||||
| 10 | Ying | Guichen Z Wong | Sanford Wong | ||||||||||
| 11 | Hui | Name List | Chris Cary | Lindsay Cary | |||||||||
| 12 | Lei | Marc S Taylor | Anita Taylor | ||||||||||
| 13 | Hong | Darshan Dave | Swati Dave | ||||||||||
| 14 | Patel | Ferlazzo Nt | Anthony J Ferlazzo | ||||||||||
Check Names | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C14 | C2 | =IF(COUNT(SEARCH(" "&A$2:A$14&" ",SUBSTITUTE(" "&J2&" "&K2&" ","-"," "))),"Name List","") |
Upvote
0
This looks like it has promise and I am super thankful. It seems to work for those first ones, but not for my entire data set. Here is my full list of names, and then an expanded array set. I can see some that I don't think are correct. I modified what you sent to include the rest of the names. =IF(COUNT(SEARCH(" "&A$2:A$1016&" ",SUBSTITUTE(" "&J6&" "&K6&" ","-"," "))),"Name List","")
| 2023_11_17 - scrape natickmlspublicrecords.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Names | Address | City | State | Zip | Owner 1 | Owner 2 | Last Sale Date | Last Sale Price | ||||||
| 2 | Smith | Name List | Frederick H Smith | Lisa G Char-Smith | 9/28/1998 | $439,900 | |||||||||
| 3 | Jo | Jeffrey A Daniels | Carrie Martin | 9/14/2016 | $920,500 | ||||||||||
| 4 | Cary | Scott A Joseph | Shelley M Joseph | 6/1/2004 | $549,000 | ||||||||||
| 5 | Berg | Rivka Kandinov | Boris Kandinov | 12/1/2020 | $1,075,000 | ||||||||||
| 6 | Nushi | Frank&s Magullion Ret | Frank Magullion Tr | 11/19/2004 | $468,500 | ||||||||||
| 7 | Wei | Name List | Michael Escovitz | Pinky Patel | 6/12/2023 | $1,550,000 | |||||||||
| 8 | Yan | Angelo L Petulla | Takako Petulla | 6/1/2012 | $822,500 | ||||||||||
| 9 | Li | Muhammad H Usmani | Tuba S Abbasi | 10/1/2021 | $880,000 | ||||||||||
| 10 | Ying | Guichen Z Wong | Sanford Wong | 9/19/2008 | $480,000 | ||||||||||
| 11 | Hui | Name List | Chris Cary | Lindsay Cary | 12/16/2020 | $1,295,000 | |||||||||
| 12 | Lei | Marc S Taylor | Anita Taylor | 2/7/2011 | $577,500 | ||||||||||
| 13 | Hong | Darshan Dave | Swati Dave | 7/24/2009 | $507,500 | ||||||||||
| 14 | Patel | Ferlazzo Nt | Anthony J Ferlazzo | 3/1/1997 | $1 | ||||||||||
| 15 | Yu | Name List | Mary V Blasberg | 7/3/1968 | $0 | ||||||||||
| 16 | Min | Vikas Gulati | Mirela Bergamo-Gulati | 8/21/2015 | $1,025,000 | ||||||||||
| 17 | Xin | Angela A Gagnon | Robert E Gagnon | 7/30/2007 | $1,039,000 | ||||||||||
| 18 | Bin | Name List | Gary C Brown | 12/3/2001 | $187,000 | ||||||||||
| 19 | Ping | Dewin Chandra | Petrisia Tjitarum | 12/18/2013 | $782,900 | ||||||||||
| 20 | Lin | Donald H Benovitz | Martha E Fagan-Benovitz | 6/9/2009 | $1 | ||||||||||
| 21 | Yong | Name List | Race Point Ventures Llc | 11/27/2002 | $865,000 | ||||||||||
| 22 | Ming | Name List | Massachusetts Tpke Auth | $0 | |||||||||||
| 23 | Ling | McGhee T | Donna L McGhee Tr | 12/8/1993 | $0 | ||||||||||
| 24 | Qing | Terri N Rawson | Michael Wakefiled | 4/17/2020 | $770,000 | ||||||||||
| 25 | Peng | Bruce D Tikofsky | Andrea M Tikofsky | 7/26/2013 | $431,000 | ||||||||||
| 26 | Qiang | Kelly Ft | Robert M Kelly Tr | 5/18/2005 | $550,000 | ||||||||||
| 27 | Yun | Elizabeth Greenberg | Jed Greenberg | 3/1/2022 | $620,000 | ||||||||||
| 28 | Jin | Nicole A Maccini | Jonathan W Maccini | 12/9/2011 | $480,000 | ||||||||||
| 29 | Rong | Name List | Susan Bornstein | 9/21/2000 | $0 | ||||||||||
| 30 | Chao | Name List | Michelle Bertrand | 9/23/2011 | $325,000 | ||||||||||
Data | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:C30 | C2 | =IF(COUNT(SEARCH(" "&A$2:A$1016&" ",SUBSTITUTE(" "&J2&" "&K2&" ","-"," "))),"Name List","") |
| 2023_11_17 - scrape natickmlspublicrecords.xlsx | |||
|---|---|---|---|
| A | |||
| 1 | Names | ||
| 2 | Smith | ||
| 3 | Jo | ||
| 4 | Cary | ||
| 5 | Berg | ||
| 6 | Nushi | ||
| 7 | Wei | ||
| 8 | Yan | ||
| 9 | Li | ||
| 10 | Ying | ||
| 11 | Hui | ||
| 12 | Lei | ||
| 13 | Hong | ||
| 14 | Patel | ||
| 15 | Yu | ||
| 16 | Min | ||
| 17 | Xin | ||
| 18 | Bin | ||
| 19 | Ping | ||
| 20 | Lin | ||
| 21 | Yong | ||
| 22 | Ming | ||
| 23 | Ling | ||
| 24 | Qing | ||
| 25 | Peng | ||
| 26 | Qiang | ||
| 27 | Yun | ||
| 28 | Jin | ||
| 29 | Rong | ||
| 30 | Chao | ||
| 31 | Gang | ||
| 32 | Yue | ||
| 33 | Jianhua | ||
| 34 | Xiaoyan | ||
| 35 | Na | ||
| 36 | Liping | ||
| 37 | Mei | ||
| 38 | Ning | ||
| 39 | Zhen | ||
| 40 | Chen | ||
| 41 | Kai | ||
| 42 | Cheng | ||
| 43 | Xiang | ||
| 44 | Haiyan | ||
| 45 | Jianping | ||
| 46 | Lihua | ||
| 47 | Nan | ||
| 48 | Xiaohong | ||
| 49 | Zhiqiang | ||
| 50 | Lan | ||
| 51 | Kun | ||
| 52 | Lijun | ||
| 53 | Xiaoli | ||
| 54 | Jianguo | ||
| 55 | Jianjun | ||
| 56 | Xiaoping | ||
| 57 | Meng | ||
| 58 | Tingting | ||
| 59 | Lijuan | ||
| 60 | Shan | ||
| 61 | Zhi | ||
| 62 | Xiaoling | ||
| 63 | Yuanyuan | ||
| 64 | Xiaodong | ||
| 65 | Xiaoming | ||
| 66 | Laoshi | ||
| 67 | |||
| 68 | Jingjing | ||
| 69 | Zhiyong | ||
| 70 | Yanping | ||
| 71 | Xiaohua | ||
| 72 | Xuan | ||
| 73 | Xiaofeng | ||
| 74 | Jianming | ||
| 75 | Weidong | ||
| 76 | Di | ||
| 77 | Huan | ||
| 78 | Xiaojun | ||
| 79 | Shuang | ||
| 80 | Qiong | ||
| 81 | Zhigang | ||
| 82 | Xiaoying | ||
| 83 | Zhiming | ||
| 84 | Jianfeng | ||
| 85 | Xiaomei | ||
| 86 | Xiaohui | ||
| 87 | Hongmei | ||
| 88 | Zhiwei | ||
| 89 | Xuemei | ||
| 90 | Long | ||
| 91 | Chunyan | ||
| 92 | Weiwei | ||
| 93 | Hongwei | ||
| 94 | Dongmei | ||
| 95 | Shu | ||
| 96 | Jinhua | ||
| 97 | Zhihua | ||
| 98 | Guoqiang | ||
| 99 | Hai | ||
| 100 | Song | ||
| 101 | Hongyan | ||
| 102 | Xiaoyu | ||
| 103 | Jianxin | ||
| 104 | Guohua | ||
| 105 | Han | ||
| 106 | |||
| 107 | Jianzhong | ||
| 108 | Chun | ||
| 109 | Zhijun | ||
| 110 | Tong | ||
| 111 | Yanling | ||
| 112 | Weihua | ||
| 113 | Shanshan | ||
| 114 | Zhihong | ||
| 115 | Liming | ||
| 116 | Yanhong | ||
| 117 | Qun | ||
| 118 | Wenjun | ||
| 119 | Lihong | ||
| 120 | Yuhua | ||
| 121 | Jianmin | ||
| 122 | Xiaolin | ||
| 123 | Quan | ||
| 124 | Junjie | ||
| 125 | Yanhua | ||
| 126 | Xiaoqing | ||
| 127 | Weimin | ||
| 128 | Jianwei | ||
| 129 | Yin | ||
| 130 | Tian | ||
| 131 | Yingying | ||
| 132 | Haibo | ||
| 133 | Weiping | ||
| 134 | Wenjie | ||
| 135 | Xian | ||
| 136 | Lingling | ||
| 137 | Ran | ||
| 138 | Qinghua | ||
| 139 | Lixin | ||
| 140 | Xiaomin | ||
| 141 | Zhihui | ||
| 142 | Huimin | ||
| 143 | Chunhua | ||
| 144 | Yanyan | ||
| 145 | Xiaowei | ||
| 146 | Haiying | ||
| 147 | Weiming | ||
| 148 | Zhenhua | ||
| 149 | Wenjing | ||
| 150 | Zhijian | ||
| 151 | Haitao | ||
| 152 | Guoliang | ||
| 153 | Liying | ||
| 154 | Zhiping | ||
| 155 | Xiaoxia | ||
| 156 | Haifeng | ||
| 157 | Jianhui | ||
| 158 | Zhimin | ||
| 159 | Guoqing | ||
| 160 | Yuping | ||
| 161 | Guoping | ||
| 162 | Weihong | ||
| 163 | Pei | ||
| 164 | Yumei | ||
| 165 | Zhangchun | ||
| 166 | Bei | ||
| 167 | Wenbin | ||
| 168 | Xiaojing | ||
| 169 | Yanli | ||
| 170 | Xinhua | ||
| 171 | Xiaoyun | ||
| 172 | Yonghong | ||
| 173 | Cong | ||
| 174 | Xiaofang | ||
| 175 | Dandan | ||
| 176 | Biao | ||
| 177 | Shaohua | ||
| 178 | Hongxia | ||
| 179 | Weiqiang | ||
| 180 | Chunmei | ||
| 181 | Yanjun | ||
| 182 | Jianhong | ||
| 183 | Yongsheng | ||
| 184 | Wenhua | ||
| 185 | Lifang | ||
| 186 | Jinfeng | ||
| 187 | Wenhui | ||
| 188 | Hang | ||
| 189 | Xuefeng | ||
| 190 | Yuhong | ||
| 191 | Heng | ||
| 192 | Weiguo | ||
| 193 | Yongqiang | ||
| 194 | Zhifeng | ||
| 195 | Yanfang | ||
| 196 | Xiaojuan | ||
| 197 | Yufeng | ||
| 198 | Jianwen | ||
| 199 | Yongjun | ||
| 200 | Weifeng | ||
| 201 | Le | ||
| 202 | Jiao | ||
| 203 | Xiaolan | ||
| 204 | Yanan | ||
| 205 | Yiming | ||
| 206 | Mingshi | ||
| 207 | |||
| 208 | Wenjuan | ||
| 209 | Lian | ||
| 210 | Jianqiang | ||
| 211 | Hongbo | ||
| 212 | Yaping | ||
| 213 | Xiaolong | ||
| 214 | Weijun | ||
| 215 | Yuting | ||
| 216 | Pengfei | ||
| 217 | Hongyu | ||
| 218 | Xiaobo | ||
| 219 | Yuling | ||
| 220 | Xiaoqin | ||
| 221 | Yuqing | ||
| 222 | Zhicheng | ||
| 223 | Zhiwen | ||
| 224 | Hongying | ||
| 225 | Yulin | ||
| 226 | Jiawei | ||
| 227 | Zhenyu | ||
| 228 | Chaohui | ||
| 229 | Yanmei | ||
| 230 | Xiaofei | ||
| 231 | Jingyi | ||
| 232 | Laoban | ||
| 233 | Jingwen | ||
| 234 | Haixia | ||
| 235 | Huiling | ||
| 236 | Linlin | ||
| 237 | Shuhua | ||
| 238 | Xiaowen | ||
| 239 | Weijie | ||
| 240 | Shuo | ||
| 241 | Yuming | ||
| 242 | Yanxia | ||
| 243 | Junsheng | ||
| 244 | Xiuying | ||
| 245 | Guanghui | ||
| 246 | Man | ||
| 247 | Jinrong | ||
| 248 | Meiling | ||
| 249 | Jianying | ||
| 250 | Zhiyuan | ||
| 251 | Fen | ||
| 252 | Hongjun | ||
| 253 | Yuying | ||
| 254 | Huifang | ||
| 255 | Zhonghua | ||
| 256 | Zhibin | ||
| 257 | Limei | ||
| 258 | Yonghua | ||
| 259 | Xuejun | ||
| 260 | Yanqing | ||
| 261 | Xinyu | ||
| 262 | Hongbin | ||
| 263 | Jiaqi | ||
| 264 | Junfeng | ||
| 265 | Jinghua | ||
| 266 | Haijun | ||
| 267 | Xudong | ||
| 268 | Guangming | ||
| 269 | Xiaorong | ||
| 270 | Guihua | ||
| 271 | Yiping | ||
| 272 | Yulan | ||
| 273 | Dongsheng | ||
| 274 | Jingli | ||
| 275 | Jinming | ||
| 276 | Weijian | ||
| 277 | Huijuan | ||
| 278 | Jianrong | ||
| 279 | Huiping | ||
| 280 | Yuzhen | ||
| 281 | Yujie | ||
| 282 | Jianbo | ||
| 283 | Zhuren | ||
| 284 | Xun | ||
| 285 | Jinping | ||
| 286 | Jiayi | ||
| 287 | Heping | ||
| 288 | Xiumei | ||
| 289 | Jinsheng | ||
| 290 | Sha | ||
| 291 | Jiahui | ||
| 292 | Junhua | ||
| 293 | Jinlong | ||
| 294 | Yimin | ||
| 295 | Huiying | ||
| 296 | Lizhen | ||
| 297 | Minghua | ||
| 298 | Yongjian | ||
| 299 | Yijun | ||
| 300 | Jiali | ||
| 301 | Minghui | ||
| 302 | Xueying | ||
| 303 | Zhuo | ||
| 304 | Caixia | ||
| 305 | Chong | ||
| 306 | Zhiying | ||
| 307 | Xiaochun | ||
| 308 | |||
| 309 | Xiaodan | ||
| 310 | Lirong | ||
| 311 | Yunfeng | ||
| 312 | Haiping | ||
| 313 | Meihua | ||
| 314 | Jinhui | ||
| 315 | Xiuzhen | ||
| 316 | Guodong | ||
| 317 | Guojun | ||
| 318 | Jiansheng | ||
| 319 | Wenting | ||
| 320 | Guohui | ||
| 321 | Feifei | ||
| 322 | Chuan | ||
| 323 | Ronghua | ||
| 324 | Jiaxin | ||
| 325 | Hongtao | ||
| 326 | Guorong | ||
| 327 | Wensheng | ||
| 328 | Liwei | ||
| 329 | Xiaobing | ||
| 330 | Guiying | ||
| 331 | Zhiguo | ||
| 332 | Yuxin | ||
| 333 | Yingjie | ||
| 334 | Yongmei | ||
| 335 | Zhengnan | ||
| 336 | Wenfeng | ||
| 337 | Yongming | ||
| 338 | Haibin | ||
| 339 | Can | ||
| 340 | Ju | ||
| 341 | Fangfang | ||
| 342 | Shuping | ||
| 343 | Jinsong | ||
| 344 | Weizhong | ||
| 345 | Xiaolei | ||
| 346 | Zhijie | ||
| 347 | Yiwen | ||
| 348 | Jinhong | ||
| 349 | Guowei | ||
| 350 | Yanhui | ||
| 351 | Zhirong | ||
| 352 | Xiaoyi | ||
| 353 | Xiaobin | ||
| 354 | Zhixiong | ||
| 355 | Sen | ||
| 356 | Hongliang | ||
| 357 | Huiyan | ||
| 358 | Zhiliang | ||
| 359 | Wenying | ||
| 360 | Jihong | ||
| 361 | Xiuhua | ||
| 362 | Xiangdong | ||
| 363 | Wenqing | ||
| 364 | Jinquan | ||
| 365 | Xinxin | ||
| 366 | Aiping | ||
| 367 | Jianlin | ||
| 368 | Meiying | ||
| 369 | Weiying | ||
| 370 | Yifeng | ||
| 371 | Yanfei | ||
| 372 | Wenbo | ||
| 373 | Hongli | ||
| 374 | Yuxiang | ||
| 375 | Weiwen | ||
| 376 | Fengying | ||
| 377 | Huiming | ||
| 378 | Huijun | ||
| 379 | Xiujuan | ||
| 380 | Jiajun | ||
| 381 | Jiaming | ||
| 382 | Lifeng | ||
| 383 | Xinmin | ||
| 384 | Wenwen | ||
| 385 | Shuying | ||
| 386 | Liwen | ||
| 387 | Chunxia | ||
| 388 | Weixiong | ||
| 389 | Yifan | ||
| 390 | Ren | ||
| 391 | Xinyi | ||
| 392 | Shuzhen | ||
| 393 | Guifang | ||
| 394 | Da | ||
| 395 | Yongping | ||
| 396 | Huizhen | ||
| 397 | Liyun | ||
| 398 | Wenyan | ||
| 399 | Yanfen | ||
| 400 | Su | ||
| 401 | Wenlong | ||
| 402 | Xinghua | ||
| 403 | Jincheng | ||
| 404 | Hailong | ||
| 405 | Jianbin | ||
| 406 | Weibin | ||
| 407 | Yixin | ||
| 408 | Yonghui | ||
| 409 | |||
| 410 | Chi | ||
| 411 | Jiawen | ||
| 412 | Xueqin | ||
| 413 | Xiangyang | ||
| 414 | Zhixin | ||
| 415 | Xiaoguang | ||
| 416 | Mingming | ||
| 417 | Zhixiang | ||
| 418 | Shengli | ||
| 419 | Yuehua | ||
| 420 | Yongquan | ||
| 421 | Zhipeng | ||
| 422 | Jialin | ||
| 423 | Kui | ||
| 424 | Caihong | ||
| 425 | Yinghua | ||
| 426 | Guanghua | ||
| 427 | Yuhui | ||
| 428 | Jinling | ||
| 429 | Jiancheng | ||
| 430 | Haiming | ||
| 431 | Xiaoxiao | ||
| 432 | Yufang | ||
| 433 | Yifei | ||
| 434 | Pingping | ||
| 435 | Xueping | ||
| 436 | Junwei | ||
| 437 | Yongqing | ||
| 438 | Yanming | ||
| 439 | Yongxin | ||
| 440 | Baohua | ||
| 441 | Jiandong | ||
| 442 | Chunsheng | ||
| 443 | Shijie | ||
| 444 | Weixin | ||
| 445 | Yonggang | ||
| 446 | Deming | ||
| 447 | Jinmei | ||
| 448 | Jianli | ||
| 449 | Yanfeng | ||
| 450 | Yunfei | ||
| 451 | Shuqin | ||
| 452 | Chunlin | ||
| 453 | Wentao | ||
| 454 | Guohong | ||
| 455 | Qiming | ||
| 456 | Shujun | ||
| 457 | Shufang | ||
| 458 | Zhaohua | ||
| 459 | Shijun | ||
| 460 | Jiajia | ||
| 461 | Yongxiang | ||
| 462 | Xiuping | ||
| 463 | Yongfeng | ||
| 464 | Jianqing | ||
| 465 | Xiulan | ||
| 466 | Qianqian | ||
| 467 | Zhihao | ||
| 468 | Guoying | ||
| 469 | Guosheng | ||
| 470 | Hairong | ||
| 471 | Zhizhong | ||
| 472 | Zhiquan | ||
| 473 | Binbin | ||
| 474 | Guirong | ||
| 475 | Zhiguang | ||
| 476 | Yingchun | ||
| 477 | Jiaying | ||
| 478 | Yumin | ||
| 479 | Qingqing | ||
| 480 | Xiaoting | ||
| 481 | Jiamin | ||
| 482 | Weiliang | ||
| 483 | Weiqing | ||
| 484 | Guoxing | ||
| 485 | Juanjuan | ||
| 486 | Yulong | ||
| 487 | Liqing | ||
| 488 | Lihui | ||
| 489 | Haiqing | ||
| 490 | Xiaoyong | ||
| 491 | Yueming | ||
| 492 | Guiping | ||
| 493 | Jingyu | ||
| 494 | Jianxiong | ||
| 495 | Yongliang | ||
| 496 | Shiming | ||
| 497 | Guoquan | ||
| 498 | Fusheng | ||
| 499 | Xuelian | ||
| 500 | Qiuping | ||
| 501 | Yusheng | ||
| 502 | Chunhong | ||
| 503 | Lingyun | ||
| 504 | Chunhui | ||
| 505 | Hongsheng | ||
| 506 | Guofeng | ||
| 507 | Mi | ||
| 508 | Wenzhong | ||
| 509 | Xiaogang | ||
| 510 | |||
| 511 | Guilan | ||
| 512 | Dongming | ||
| 513 | Yuliang | ||
| 514 | Yunlong | ||
| 515 | Zhifang | ||
| 516 | Jingwei | ||
| 517 | Xiufang | ||
| 518 | Huiqin | ||
| 519 | Haihua | ||
| 520 | Wenyi | ||
| 521 | Leao | ||
| 522 | Chunyu | ||
| 523 | Guoxin | ||
| 524 | Yongjie | ||
| 525 | Shuyi | ||
| 526 | Xiaoliang | ||
| 527 | Chunling | ||
| 528 | Xiuling | ||
| 529 | Meifang | ||
| 530 | Zhiqing | ||
| 531 | Jinliang | ||
| 532 | Jiayu | ||
| 533 | Chunli | ||
| 534 | Yongli | ||
| 535 | Guoxiang | ||
| 536 | Weilin | ||
| 537 | Jinxiang | ||
| 538 | Weixing | ||
| 539 | Xiuqin | ||
| 540 | Xuehua | ||
| 541 | Minhua | ||
| 542 | Shuqing | ||
| 543 | Wenming | ||
| 544 | Xiaoyang | ||
| 545 | Guizhen | ||
| 546 | Hailin | ||
| 547 | Zhenzhen | ||
| 548 | Furong | ||
| 549 | Qingfeng | ||
| 550 | Shujuan | ||
| 551 | Jianmei | ||
| 552 | Desheng | ||
| 553 | Jingbo | ||
| 554 | Shumin | ||
| 555 | Guilin | ||
| 556 | Guoming | ||
| 557 | Yilin | ||
| 558 | Jianliang | ||
| 559 | Yurong | ||
| 560 | Weiquan | ||
| 561 | Shiping | ||
| 562 | Xueming | ||
| 563 | Wenhao | ||
| 564 | Yucheng | ||
| 565 | Rongrong | ||
| 566 | Yihong | ||
| 567 | Xiaopeng | ||
| 568 | Xuewen | ||
| 569 | Mo | ||
| 570 | Guixiang | ||
| 571 | Ruihua | ||
| 572 | Jue | ||
| 573 | Zhichao | ||
| 574 | Yunxia | ||
| 575 | Jiangang | ||
| 576 | Weicheng | ||
| 577 | Yulian | ||
| 578 | Junhui | ||
| 579 | Caiyun | ||
| 580 | Jinyan | ||
| 581 | Yunhua | ||
| 582 | Zhongming | ||
| 583 | Meizhen | ||
| 584 | Jinghui | ||
| 585 | Yanbin | ||
| 586 | Cuiping | ||
| 587 | Haihong | ||
| 588 | Jianfang | ||
| 589 | Shaojun | ||
| 590 | Guangyu | ||
| 591 | Chaoyang | ||
| 592 | Haiyang | ||
| 593 | Shihua | ||
| 594 | Xiaolu | ||
| 595 | Jianfei | ||
| 596 | Shaofeng | ||
| 597 | Shufen | ||
| 598 | Shulin | ||
| 599 | Qingyun | ||
| 600 | Wenping | ||
| 601 | Liling | ||
| 602 | Guozhong | ||
| 603 | Liyuan | ||
| 604 | Shasha | ||
| 605 | Huiqing | ||
| 606 | Yanting | ||
| 607 | Zhongliang | ||
| 608 | Hongjie | ||
| 609 | Xiangyu | ||
| 610 | Weiguang | ||
| 611 | |||
| 612 | Shuming | ||
| 613 | Jinyuan | ||
| 614 | Guomin | ||
| 615 | Xueyan | ||
| 616 | Xiaoqian | ||
| 617 | Yongkang | ||
| 618 | Yuwen | ||
| 619 | Xiaoqiang | ||
| 620 | Xiaoning | ||
| 621 | Wenwei | ||
| 622 | Junyi | ||
| 623 | Yujuan | ||
| 624 | Shiwei | ||
| 625 | Jianyong | ||
| 626 | Yangyang | ||
| 627 | Shuxian | ||
| 628 | Shaoping | ||
| 629 | Xiu | ||
| 630 | Jinwei | ||
| 631 | Mingliang | ||
| 632 | Zhongyi | ||
| 633 | Xianfeng | ||
| 634 | Hongbing | ||
| 635 | Chunying | ||
| 636 | Junying | ||
| 637 | Beibei | ||
| 638 | Honglin | ||
| 639 | Xinping | ||
| 640 | Mingjun | ||
| 641 | Xiuyun | ||
| 642 | Junhong | ||
| 643 | Zhilin | ||
| 644 | Liqiang | ||
| 645 | Xuesong | ||
| 646 | Chunming | ||
| 647 | Xueli | ||
| 648 | Xiaoyuan | ||
| 649 | Yisheng | ||
| 650 | Yonglin | ||
| 651 | Yanjie | ||
| 652 | Xiaozhen | ||
| 653 | Yufei | ||
| 654 | Shuyan | ||
| 655 | Yuwei | ||
| 656 | Yongzhong | ||
| 657 | Dongdong | ||
| 658 | Shimin | ||
| 659 | Shumei | ||
| 660 | Xuefei | ||
| 661 | Yanwen | ||
| 662 | Huili | ||
| 663 | Changqing | ||
| 664 | Jialiang | ||
| 665 | Yinghui | ||
| 666 | Jiping | ||
| 667 | Yushan | ||
| 668 | Meijuan | ||
| 669 | Wenxin | ||
| 670 | Wenliang | ||
| 671 | Fenghua | ||
| 672 | Xiangjun | ||
| 673 | Shuwen | ||
| 674 | Haidong | ||
| 675 | Junming | ||
| 676 | Yaling | ||
| 677 | Xinming | ||
| 678 | Lanying | ||
| 679 | Yanmin | ||
| 680 | Jinwen | ||
| 681 | Tai | ||
| 682 | Jincai | ||
| 683 | Xiaona | ||
| 684 | Haiyun | ||
| 685 | Wenqi | ||
| 686 | Junping | ||
| 687 | Guolin | ||
| 688 | Jianxing | ||
| 689 | Yanlin | ||
| 690 | Jinying | ||
| 691 | Yongfu | ||
| 692 | Jinxing | ||
| 693 | Shuhong | ||
| 694 | Jieying | ||
| 695 | Hongxing | ||
| 696 | Huihui | ||
| 697 | Baolin | ||
| 698 | Guobin | ||
| 699 | Xiaojian | ||
| 700 | Yiqing | ||
| 701 | Qinglin | ||
| 702 | Qiaoling | ||
| 703 | Yingjun | ||
| 704 | Siyu | ||
| 705 | Xianghua | ||
| 706 | Donghua | ||
| 707 | Gui | ||
| 708 | Xiufeng | ||
| 709 | Siyuan | ||
| 710 | Changsheng | ||
| 711 | Jiming | ||
| 712 | |||
| 713 | Xueling | ||
| 714 | Suzhen | ||
| 715 | Weisheng | ||
| 716 | Jianling | ||
| 717 | Weiling | ||
| 718 | Wenyuan | ||
| 719 | Chunlei | ||
| 720 | Shuchun | ||
| 721 | Hongming | ||
| 722 | Jianyi | ||
| 723 | Jinlian | ||
| 724 | Jinlin | ||
| 725 | Jiarong | ||
| 726 | Huiwen | ||
| 727 | Xiuhong | ||
| 728 | Meirong | ||
| 729 | Zhongwei | ||
| 730 | Xiaofen | ||
| 731 | Wenchao | ||
| 732 | Yanqiu | ||
| 733 | Hongyi | ||
| 734 | Yiwei | ||
| 735 | Lingli | ||
| 736 | Ailing | ||
| 737 | Wenjian | ||
| 738 | Liting | ||
| 739 | Zijian | ||
| 740 | Yanchun | ||
| 741 | Mingjie | ||
| 742 | Yanzhen | ||
| 743 | Huifen | ||
| 744 | Shun | ||
| 745 | Zhendong | ||
| 746 | Chan | ||
| 747 | De | ||
| 748 | Jinxia | ||
| 749 | Jinhai | ||
| 750 | Tiantian | ||
| 751 | Shuguang | ||
| 752 | Yujing | ||
| 753 | Guoxiong | ||
| 754 | Xueqing | ||
| 755 | Haisheng | ||
| 756 | Zhiling | ||
| 757 | Dongping | ||
| 758 | Xie | ||
| 759 | Lingyan | ||
| 760 | Teng | ||
| 761 | Zhilong | ||
| 762 | Shurong | ||
| 763 | Pengcheng | ||
| 764 | Chunfeng | ||
| 765 | Zhenxing | ||
| 766 | Shuling | ||
| 767 | Wenqiang | ||
| 768 | Yajing | ||
| 769 | Zhenghua | ||
| 770 | Weiyi | ||
| 771 | Huiyi | ||
| 772 | Xuemin | ||
| 773 | Shiying | ||
| 774 | Wenxiang | ||
| 775 | Xiaotong | ||
| 776 | Jianan | ||
| 777 | Jiewen | ||
| 778 | Xiurong | ||
| 779 | Shenghua | ||
| 780 | Miaomiao | ||
| 781 | Wenjin | ||
| 782 | Qingping | ||
| 783 | Jiahao | ||
| 784 | Dongliang | ||
| 785 | Mingyu | ||
| 786 | Chunrong | ||
| 787 | Chunlan | ||
| 788 | Jinfang | ||
| 789 | Chunfang | ||
| 790 | Xinyan | ||
| 791 | Hongfei | ||
| 792 | Xiaonan | ||
| 793 | Jiahua | ||
| 794 | Zhisheng | ||
| 795 | Chunping | ||
| 796 | Jianyu | ||
| 797 | Yongyi | ||
| 798 | Jianxiang | ||
| 799 | Yanrong | ||
| 800 | Xiaoqi | ||
| 801 | Guojian | ||
| 802 | Minjie | ||
| 803 | Shirong | ||
| 804 | Yongzhi | ||
| 805 | Jingyuan | ||
| 806 | Hongping | ||
| 807 | Wan | ||
| 808 | Weiqi | ||
| 809 | Ziqiang | ||
| 810 | Fuqiang | ||
| 811 | Mingxia | ||
| 812 | Xinrong | ||
| 813 | |||
| 814 | Xianhua | ||
| 815 | Xiaoxin | ||
| 816 | Jintao | ||
| 817 | Mingzhu | ||
| 818 | Yuehong | ||
| 819 | Chaoxia | ||
| 820 | Shaoying | ||
| 821 | Qiuhong | ||
| 822 | Shuhui | ||
| 823 | Weirong | ||
| 824 | Mingsheng | ||
| 825 | Jianguang | ||
| 826 | Yongxing | ||
| 827 | Huihua | ||
| 828 | Wenguang | ||
| 829 | Yajuan | ||
| 830 | Huilin | ||
| 831 | Meilan | ||
| 832 | Mingli | ||
| 833 | Jiasheng | ||
| 834 | Junjun | ||
| 835 | Yawen | ||
| 836 | Guozhen | ||
| 837 | Shihong | ||
| 838 | Jinshan | ||
| 839 | Xiaoxue | ||
| 840 | Lixiang | ||
| 841 | Yuchen | ||
| 842 | Shaohui | ||
| 843 | Fengming | ||
| 844 | Qunying | ||
| 845 | Qiuyan | ||
| 846 | Shuyun | ||
| 847 | Xiuqing | ||
| 848 | Guangrong | ||
| 849 | Zhongping | ||
| 850 | Xuefang | ||
| 851 | Zhiyun | ||
| 852 | Liqiong | ||
| 853 | Zhenguo | ||
| 854 | Jianquan | ||
| 855 | Peipei | ||
| 856 | Hongyun | ||
| 857 | Peiyu | ||
| 858 | Shaomei | ||
| 859 | Donghui | ||
| 860 | Qingyu | ||
| 861 | Guisheng | ||
| 862 | Xiaochen | ||
| 863 | Jiehua | ||
| 864 | Yongchang | ||
| 865 | Haoran | ||
| 866 | Jianchun | ||
| 867 | Xianjun | ||
| 868 | Yaohua | ||
| 869 | Yongcheng | ||
| 870 | Wenhong | ||
| 871 | Chunxiang | ||
| 872 | Jianlong | ||
| 873 | Xianming | ||
| 874 | Shiyu | ||
| 875 | Ximing | ||
| 876 | Yanwei | ||
| 877 | Yinghong | ||
| 878 | Weijia | ||
| 879 | Dequan | ||
| 880 | Yueping | ||
| 881 | Honghua | ||
| 882 | Shuxia | ||
| 883 | Xinsheng | ||
| 884 | Yueying | ||
| 885 | Jiajie | ||
| 886 | Jinlan | ||
| 887 | Wenfang | ||
| 888 | Qingsong | ||
| 889 | Shuting | ||
| 890 | Zhidong | ||
| 891 | Shaoqing | ||
| 892 | Shilin | ||
| 893 | Xueliang | ||
| 894 | Shang | ||
| 895 | Hongjuan | ||
| 896 | Lee | ||
| 897 | Zhenwei | ||
| 898 | Dangdang | ||
| 899 | Shaohong | ||
| 900 | Huaying | ||
| 901 | Hailing | ||
| 902 | Xinwei | ||
| 903 | Jiabin | ||
| 904 | Yanqin | ||
| 905 | Xiangrong | ||
| 906 | Qingmei | ||
| 907 | Songlin | ||
| 908 | Zhengrong | ||
| 909 | Peihua | ||
| 910 | Xinhui | ||
| 911 | Jialing | ||
| 912 | Jiacheng | ||
| 913 | Yanying | ||
| 914 | |||
| 915 | Qifeng | ||
| 916 | Lisheng | ||
| 917 | Xiaomeng | ||
| 918 | Yunsheng | ||
| 919 | Jinghong | ||
| 920 | Yuchun | ||
| 921 | Guocheng | ||
| 922 | Shulan | ||
| 923 | Jiahong | ||
| 924 | Rongsheng | ||
| 925 | Yingyi | ||
| 926 | Jingming | ||
| 927 | Yongbin | ||
| 928 | Yahui | ||
| 929 | Huirong | ||
| 930 | Jinbiao | ||
| 931 | Zhiqin | ||
| 932 | Donghai | ||
| 933 | Xinyuan | ||
| 934 | Zhongmin | ||
| 935 | Xiping | ||
| 936 | Yanbing | ||
| 937 | Chenglin | ||
| 938 | Mengmeng | ||
| 939 | Xuezhen | ||
| 940 | Fengqin | ||
| 941 | Guihong | ||
| 942 | Guiming | ||
| 943 | Jiaxing | ||
| 944 | Jianyun | ||
| 945 | Junling | ||
| 946 | Wenzhi | ||
| 947 | Changming | ||
| 948 | Fengxia | ||
| 949 | Jinbao | ||
| 950 | Zhixing | ||
| 951 | Jingping | ||
| 952 | Suping | ||
| 953 | Xinjian | ||
| 954 | Yihui | ||
| 955 | Yingxin | ||
| 956 | Ban | ||
| 957 | Tiejun | ||
| 958 | Weifang | ||
| 959 | Shaowei | ||
| 960 | Guangsheng | ||
| 961 | Qiuxia | ||
| 962 | Fengling | ||
| 963 | Hongling | ||
| 964 | Qingwen | ||
| 965 | Junyan | ||
| 966 | Guangwei | ||
| 967 | Hongzhi | ||
| 968 | Jieling | ||
| 969 | Huanhuan | ||
| 970 | Daming | ||
| 971 | Weizhen | ||
| 972 | Xiaoshan | ||
| 973 | Mingxing | ||
| 974 | Guimei | ||
| 975 | Shaobo | ||
| 976 | Xuehong | ||
| 977 | Lizhong | ||
| 978 | Huixia | ||
| 979 | Shiqiang | ||
| 980 | Xinying | ||
| 981 | Xinyue | ||
| 982 | Yadong | ||
| 983 | Tongxue | ||
| 984 | Xiaoxi | ||
| 985 | Huajun | ||
| 986 | Ruifang | ||
| 987 | Chengjun | ||
| 988 | Qinghui | ||
| 989 | Qingshan | ||
| 990 | Lijing | ||
| 991 | Shaoling | ||
| 992 | Honghui | ||
| 993 | Libing | ||
| 994 | Xiaohu | ||
| 995 | Guofang | ||
| 996 | Hongyuan | ||
| 997 | Lanlan | ||
| 998 | Yiting | ||
| 999 | Huixian | ||
| 1000 | Qiuhua | ||
| 1001 | Ronghui | ||
| 1002 | Xiangming | ||
| 1003 | Xiufen | ||
| 1004 | Lichun | ||
| 1005 | Peijun | ||
| 1006 | Jisheng | ||
| 1007 | Shaoming | ||
| 1008 | Zihao | ||
| 1009 | Xuelin | ||
| 1010 | Yunpeng | ||
| 1011 | Jiangtao | ||
| 1012 | Jieping | ||
| 1013 | Ruiping | ||
| 1014 | Xianghong | ||
| 1015 | Junmin | ||
| 1016 | Qingyuan | ||
Data | |||
Upvote
0
Peter_SSs
MrExcel MVP, Moderator
- Joined
- May 28, 2005
- Messages
- 66,058
- Office Version
- 365
- Platform
- Windows
It would have been helpful to identify those, or at least a few of them. Never-the-less I think I now know the ones you mean.
That is because your sample data set was not representative of your entire data set.
In your sample, there were no blank cells in the columns J:K data and no blank cells in the column A data. In this data set you have blank cells in both.
Try this instead.
Excel Formula:
=LET(n,FILTER(A$2:A$1016,A$2:A$1016<>""),IF(COUNT(SEARCH(" "&n&" ",SUBSTITUTE(" "&J2&" "&K2&" ","-"," "))),"Name List",""))
Upvote
0
Similar threads
- Solved
