ABCDEFGHIJ
1
Here is the current data set to work with.
A:B - List of names in two columns.
D - Required format when CONCAT is performed.
RESULTS: Using a single array formula, I would like to conditionally CONCAT Col A (Name1), and Col B (Name2), and seperate using "_" delimiter in Col F. The condition being that it must CONCAT based on the "Valid Name Formats helper Col D."LEGEND
2
2 Columns with namesValid Name FormatsName not found in column D
3
Name 1Name 2First Last OR First Last_First LastMy Current Formula (Does not work)Expected ResultsName found in column D, but incorrect format. Should be Name 2_Name1
4
Michelle RobinsonJulie MorganAdam Coleman_Virginia MartinMichelle Robinson_Julie MorganJulie Morgan_Michelle RobinsonName found in column D
5
James BaileyJacqueline BarnesAlan Flores_Gary WilliamsJames Bailey_Jacqueline BarnesJames Bailey_Jacqueline Barnes
6
Joe HernandezKathleen WilsonAmy Coleman_Janet HallJoe Hernandez_Kathleen WilsonJoe Hernandez_Kathleen Wilson
7
Gary WilliamsAlan FloresCheryl Williams_Cheryl MooreGary Williams_Alan FloresAlan Flores_Gary Williams
8
Michelle RobinsonTammy PerezDavid Murphy_Doris JenkinsMichelle Robinson_Tammy PerezTammy Perez_Michelle Robinson
9
John ThomasCatherine PerryDiana Wilson_Alice HughesJohn Thomas_Catherine PerryJohn Thomas_Catherine Perry
10
Amy ColemanJanet HallDiana Wilson_Betty GonzalesAmy Coleman_Janet HallAmy Coleman_Janet Hall
11
Gerald SimmonsEugene Rodriguez_Norma ClarkGerald SimmonsGerald Simmons
12
Virginia MartinJoe TaylorFrances ClarkVirginia Martin_Joe TaylorVirginia Martin_Joe Taylor
13
Cheryl MooreCheryl WilliamsGerald SimmonsCheryl Moore_Cheryl WilliamsCheryl Williams_Cheryl Moore
14
Susan CampbellVictor RobertsHarold White_Tina TorresSusan Campbell_Victor RobertsSusan Campbell_Victor Roberts
15
David MurphyDoris JenkinsHeather Garcia_Keith LeeDavid Murphy_Doris JenkinsDavid Murphy_Doris Jenkins
16
Virginia MartinJames Bailey_Jacqueline BarnesVirginia Martin
17
Diane KingMaria SanchezJane Evans_George HowardDiane King_Maria SanchezMaria Sanchez_Diane King
18
Virginia MartinAdam ColemanJoe Hernandez_Kathleen WilsonVirginia Martin_Adam ColemanAdam Coleman_Virginia Martin
19
Jane EvansGeorge HowardJohn Thomas_Catherine PerryJane Evans_George HowardJane Evans_George Howard
20
Robert GreenJohnny Kelly_Diana WilsonRobert GreenRobert Green
21
Heather GarciaKeith LeeJulie Morgan_Michelle RobinsonHeather Garcia_Keith LeeHeather Garcia_Keith Lee
22
Tina TorresHarold WhiteMaria Sanchez_Diane KingTina Torres_Harold WhiteHarold White_Tina Torres
23
Diana WilsonBetty GonzalesRobert GreenDiana Wilson_Betty GonzalesDiana Wilson_Betty Gonzales
24
Eugene RodriguezNorma ClarkShirley Simmons_Evelyn SanchezEugene Rodriguez_Norma ClarkEugene Rodriguez_Norma Clark
25
Frances ClarkSusan Campbell_Victor RobertsFrances ClarkFrances Clark
26
Evelyn SanchezShirley SimmonsTammy Perez_Michelle RobinsonEvelyn Sanchez_Shirley SimmonsShirley Simmons_Evelyn Sanchez
27
Diana WilsonAlice HughesVirginia Martin_Joe TaylorDiana Wilson_Alice HughesDiana Wilson_Alice Hughes
28
Diana WilsonJohnny KellyDiana Wilson_Johnny KellyJohnny Kelly_Diana Wilson
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100