COMBINGING IF AND &

[Jewells0905]

Hi,

I am trying to write a formula to generate either Ms. or Mr. based off the gender in column C, then the address for each person, I have =A2&" "&B2&" "&D2&" "&E2&" "&F2&" "&G2 which generates the first, last name, and address, and I have =IF(C2="F","Ms.", "Mr.") which works to generate either Ms or Mr, however, when i try to combine them I get an error message, or #N/A stating too many arguments.

=IF(C2="F","Ms.","Mr.",A2&" "&B2&" "&D2&" "&E2&" "&F2&" "&G2) ---- gives #N/A - IN BELOW
=CONCATENATE(IF(C2="F","Ms.", "Mr.")A2," ",B2," ",D2," ", E2, " ",F2, " ", G2) ----- parse error
=CONCATENATE(IF(C2="F","Ms.", "Mr.")C2,",",A2," ",B2," ", D2," ",E2," ",F2," ",G2) ---- parse error
=IF(C9="F","Ms.", "Mr."),CONCATENATE(A9," ",B9," ",D9," ",E9," ",F9," ",G9) ----- parse error

In the below, I have

Mary	Smith	f	787	Madison	Lee's Summit	MO	#N/A
Robert	Johnson	m	3728	Hilltop	Blue Springs	MO	Mary Smith 787 Madison Lee's Summit MO
John	Williams	m	8555	Aspen	Lee's Summit	MO
Linda	Brown	f	9299	Sunrise	Raytown	MO
David	Jones	m	1205	Maple	Blue Springs	MO
Barbara	Garcia	f	4926	Cardinal	Lee's Summit	MO
Susan	Miller	f	7190	Railroad	Raytown	MO
Joseph	Davis	m	1301	Lakeview	Grain Valley	MO
Thomas	Rodriguez	m	868	Chestnut	Gladstone	MO

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	Mary	Smith	f	787	Madison	Lee's Summit	MO	Ms Mary Smith 787 Madison Lee's Summit MO
3	Robert	Johnson	m	3728	Hilltop	Blue Springs	MO	Mr Robert Johnson 3728 Hilltop Blue Springs MO
4	John	Williams	m	8555	Aspen	Lee's Summit	MO	Mr John Williams 8555 Aspen Lee's Summit MO
5	Linda	Brown	f	9299	Sunrise	Raytown	MO	Ms Linda Brown 9299 Sunrise Raytown MO
6	David	Jones	m	1205	Maple	Blue Springs	MO	Mr David Jones 1205 Maple Blue Springs MO
7	Barbara	Garcia	f	4926	Cardinal	Lee's Summit	MO	Ms Barbara Garcia 4926 Cardinal Lee's Summit MO
8	Susan	Miller	f	7190	Railroad	Raytown	MO	Ms Susan Miller 7190 Railroad Raytown MO
9	Joseph	Davis	m	1301	Lakeview	Grain Valley	MO	Mr Joseph Davis 1301 Lakeview Grain Valley MO
10	Thomas	Rodriguez	m	868	Chestnut	Gladstone	MO	Mr Thomas Rodriguez 868 Chestnut Gladstone MO
Sheet6

Cell Formulas
Range	Formula
H2:H10	H2	=TEXTJOIN(" ",,IF(C2="f","Ms","Mr"),CHOOSECOLS(A2:G2,1,2,4,5,6,7))

 

[Jewells0905]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1
2	Mary	Smith	f	787	Madison	Lee's Summit	MO	Ms Mary Smith 787 Madison Lee's Summit MO
3	Robert	Johnson	m	3728	Hilltop	Blue Springs	MO	Mr Robert Johnson 3728 Hilltop Blue Springs MO
4	John	Williams	m	8555	Aspen	Lee's Summit	MO	Mr John Williams 8555 Aspen Lee's Summit MO
5	Linda	Brown	f	9299	Sunrise	Raytown	MO	Ms Linda Brown 9299 Sunrise Raytown MO
6	David	Jones	m	1205	Maple	Blue Springs	MO	Mr David Jones 1205 Maple Blue Springs MO
7	Barbara	Garcia	f	4926	Cardinal	Lee's Summit	MO	Ms Barbara Garcia 4926 Cardinal Lee's Summit MO
8	Susan	Miller	f	7190	Railroad	Raytown	MO	Ms Susan Miller 7190 Railroad Raytown MO
9	Joseph	Davis	m	1301	Lakeview	Grain Valley	MO	Mr Joseph Davis 1301 Lakeview Grain Valley MO
10	Thomas	Rodriguez	m	868	Chestnut	Gladstone	MO	Mr Thomas Rodriguez 868 Chestnut Gladstone MO
Sheet6

Cell Formulas
Range	Formula
H2:H10	H2	=TEXTJOIN(" ",,IF(C2="f","Ms","Mr"),CHOOSECOLS(A2:G2,1,2,4,5,6,7))

worked perfectly! thank you

 

[Fluff]

You're welcome & thanks for the feedback.