Smarter way for this IF Formula to work

[FredFlamingo]

Hi all,

I have a rather disgusting looking IF formula in work, i have pasted it below but do not expect people to read it, but i am wondering, is there some smarter way to do what i want it to do, basically one cell put in a short code E.g. E2004 and it spits out a set sentence, sometimes additional info can be added at the end of it and this is what column AC is for. if anyone has any ideas how to tame this beast, please offer some suggestions.

=IF(AD2<>"",(AD2),IF(F2="E2004",(CONCATENATE(AB2,AY2," ",H2," ","Househunting ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2007",(CONCATENATE(AB2,AY2," ",H2," ","Settling In Service ",X2,Z2)),IF(F2="E2002",(CONCATENATE(AB2,AY2," ",H2," ","Househunting ","(",TEXT(I2,"m/d/yy"),")"," Lodging ","(",L2,")")),IF(F2="E2008",(CONCATENATE(AB2,AY2," ",H2," ","Area Tour ",X2)),IF(F2="E2010",(CONCATENATE(AB2,AY2," ",H2," ","Home Finding ",X2)),IF(F2="E2011",(CONCATENATE(AB2,AY2," ",H2," ","Settling In ",Z2)),IF(F2="E2020",(CONCATENATE(AB2,AY2," ",H2," ","Departure Services ",AC2)),IF(F2="E2021",(CONCATENATE(AB2,AY2," ",H2," ","Departure Services Vendor Fee",AC2)),IF(F2="E2123",(CONCATENATE(AB2,AY2," ",H2," ","Home Sale Title Coordination Fees",AC2)),IF(F2="E2124",(CONCATENATE(AB2,AY2," ",H2," ","Home Sale ","(",TEXT(I2,"m/d/yy"),")"," Inspection ","(",AA2,")")),IF(F2="E2125",(CONCATENATE(AB2,AY2," ",H2," ","Home Sale Appraisal ",AC2)),IF(F2="E2133",(CONCATENATE(AB2,AY2," ",H2," ","Inventory - HOA Dues ",AC2)),IF(F2="E2134",(CONCATENATE(AB2,AY2," ",H2," ","Inventory - Maintenance ",AC2)),IF(F2="E2135",(CONCATENATE(AB2,AY2," ",H2," ","Inventory - Mortgage Payment ",AC2)),IF(F2="E2136",(CONCATENATE(AB2,AY2," ",H2," ","Inventory - Utilities ",AC2)),IF(F2="E2102",(CONCATENATE(AB2,AY2," ",H2," ","Home Purchase Closing Costs ",AC2)), IF(F2="E2140",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Lodging ","(",L2,") Origin")),IF(F2="E2145",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Lodging ","(",L2,")")),IF(F2="E2146",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Car Rental ","(",L2,")")),IF(F2="E2148",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),") ",AC2)),IF(F2="E2151",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living Vendor Fee ",AC2)),IF(F2="E2155",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),") ","Airport Taxi")),IF(F2="E2157",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Parking ","(",L2,")")),IF(F2="E2158",(CONCATENATE(AB2,AY2," ",H2," ","Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Furniture Rental ","(",L2,")")),IF(F2="E2161",(CONCATENATE(AB2,AY2," ",H2," ","Business Final Move ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2162",(CONCATENATE(AB2,AY2," ",H2," ","Bus Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Lodging ","(",L2,")")),IF(F2="E2166",(CONCATENATE(AB2,AY2," ",H2," ","Bus Temp Living ","(",TEXT(I2,"m/d/yy"),")"," Car Rental ","(",L2,")")), IF(F2="E2167",(CONCATENATE(AB2,AY2," ",H2," ","Bus Temp Living Vendor Fee ",AC2)),IF(F2="E2180",(CONCATENATE(AB2,AY2," ",H2," ","Business Final Move ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2190",(CONCATENATE(AB2,AY2," ",H2," ","Candidate Trip ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2200",(CONCATENATE(AB2,AY2," ",H2," ","Home Leave ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2201",(CONCATENATE(AB2,AY2," ",H2," ","Home Leave ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2223",(CONCATENATE(AB2,AY2," ",H2," ","Property Management Services ","(",TEXT(I2, "mmmm") & " " & TEXT(I2, "yyyy"),")",)), IF(F2="E2226",(CONCATENATE(AB2,AY2," ",H2," ","Property Management Services ","(",TEXT(I2, "mmmm") & " " & TEXT(I2, "yyyy"),") VAT",)),IF(F2="E2240",(CONCATENATE(AB2,AY2," ",H2," ","Host Housing Vendor Fee ",AC2)),IF(F2="E2241",(CONCATENATE(AB2,AY2," ",H2," ","Real Estate Agency Fee ")),IF(F2="E2244",(CONCATENATE(AB2,AY2," ",H2," ","Host Housing Security Deposit ")), IF(F2="E2246",(CONCATENATE(AB2,AY2," ",H2," ","Host Housing ","(",TEXT(I2, "mmmm") & " " & TEXT(I2, "yyyy"),") ",AC2)),IF(F2="E2247",(CONCATENATE(AB2,AY2," ",H2," ","Host Housing ","(",TEXT(I2,"m/d/yy"),")"," Furniture Rental ",)),IF(F2="E2260",(CONCATENATE(AB2,AY2," ",H2," ","Cross Cultural Training",Y2)),IF(F2="E2261",(CONCATENATE(AB2,AY2," ",H2," ","Language Training ",Y2,AC2)),IF(F2="E2262",(CONCATENATE(AB2,AY2," ",H2," ","Cross Cultural Training",Y2," ",AC2)),IF(F2="E2263",(CONCATENATE(AB2,AY2," ",H2," ","Language Training ",Y2," ",AC2)),IF(F2="E2264",(CONCATENATE(AB2,AY2," ",H2," ","Spousal Assistance ",Y2)),IF(F2="E2266",(CONCATENATE(AB2,AY2," ",H2," ","Mercer Fees ")), IF(F2="E2270",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)),IF(F2="E2271",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance ",AC2)), IF(F2="E2272",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)),IF(F2="E2273",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)), IF(F2="E2274",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)),IF(F2="E2275",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance ",AC2)), IF(F2="E2276",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)), IF(F2="E2277",(CONCATENATE(AB2,AY2," ",H2," ","Immigration Assistance - ",AC2)),IF(F2="E2282",(CONCATENATE(AB2,AY2," ",H2," ","Host Auto Lease ","(",TEXT(I2,"m/d/yy"),") ",AC2)),IF(F2="E2290",(CONCATENATE(AB2,AY2," ",H2," ","School Tutition Deposit ",AC2)),IF(F2="E2291",(CONCATENATE(AB2,AY2," ",H2," ","School Tutition ",AC2)),IF(F2="E2292",(CONCATENATE(AB2,AY2," ",H2," ","School Transportation ",AC2)),IF(F2="E2350",(CONCATENATE(AB2,AY2," ",H2," ","Discard/Donate")), IF(F2="E2351",(CONCATENATE(AB2,AY2," ",H2," ","Household Goods Auto - ",AC2)), IF(F2="E2352",(CONCATENATE(AB2,AY2," ",H2," ","Household Goods Pet Shipment ",AC2)),IF(F2="E2355",(CONCATENATE(AB2,AY2," ",H2," ","Household Goods ","(",I2,,") ","Perm Storage",AC2)),IF(F2="E2356",(CONCATENATE(AB2,AY2," ",H2," ","Household Goods Vat ",AC2)),IF(F2="E2370",(CONCATENATE(AB2,AY2," ",H2," ","Final Move ","(",TEXT(I2,"m/d/yy"),")"," Airfare ",AC2)),IF(F2="E2374",(CONCATENATE(AB2,AY2," ",H2," ","Final Move ","(",TEXT(I2,"m/d/yy"),")"," Lodging ","(",L2,")")),IF(F2="E2421",(CONCATENATE(AB2,AY2," ",H2," ","Tax Consultation ",AC2)),IF(F2="E2438",(CONCATENATE(AB2,AY2," ",H2," ","Health Insurance Payment Allowance ","(",TEXT(I2, "mmmm") & " " & TEXT(I2, "yyyy"),")",)),IF(F2="E2501",(CONCATENATE(AB2,AY2," ",H2," ","Rental Assistance ",X2)),IF(F2="E2283",(CONCATENATE(AB2,AY2," ",H2," ","Host Auto Lease VAT ","(",TEXT(I2,"m/d/yy"),") ",AC2)),(""))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[Joe4]

There are a few things you may be able to do, i.e.:

1. Create a lookup table, that has the value you want to look-up (i.e. "E2004") and have columns of data related to that that you might want to return.
Then you can use functions like VLOOKUP or INDEX/MATCH to return the related information you want.
See this is you are not familiar with VLOOKUP: How to use the Excel VLOOKUP function | Exceljet

2. Create your own User Defined Function (UDF) in VBA that does what you want. The advantage to doing it this way is it is a little easier to read and manage (as it will appear in a structured, more readable format). And if you ever have to make changes to it, you only need to update the one User Defined Function in VBA, and not every instance of the formula on your worksheet. There are lots of articles and tutorials on creating UDFs that you can find with a simple Google search.

 

[Snakehips]

@FredFlamingo I totally agree with what @Joe4 has posted.

However, curiosity got me looking at this.
The below approach is, arguably, a bit less 'disgusting' than your original.
Your formula is a bit boggling but I think the Substitutions in my formula are enough to cope with creating all of your sentences.
Hopefully, the examples in the Sentance Lookup table indicate how they are constructed. Basic text and including strings bounded by single quotes that represent the data that you wish to have substituted. Hope that makes sense?

MRXLMAY21.xlsm
	A	B				F	G	H	I	J	K	L											W	X	Y	Z	AA	AB	AC	AD																					AY
1
2	AB-Stuff2 AY-Stuff Stuff in H2 Househunting (22/06/01) Airfare Other					E2002		Stuff in H2	22/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff2	Other																						AY-Stuff
3	AB-Stuff3 AY-Stuff Stuff in H3 Househunting (23/06/01) Lodging L-Stuff					E2004		Stuff in H3	23/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff3	Other																						AY-Stuff
4	AB-Stuff4 AY-Stuff Stuff in H4 Settling In Service X-Stuff Z-Stuff					E2007		Stuff in H4	24/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff4	Other																						AY-Stuff
5	AB-Stuff5 AY-Stuff Stuff in H5 Area Tour X-Stuff					E2008		Stuff in H5	25/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff5	Other																						AY-Stuff
6	AB-Stuff6 AY-Stuff Stuff in H6 Homefinding X-Stuff					E2010		Stuff in H6	26/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff6	Other																						AY-Stuff
7	AB-Stuff7 AY-Stuff Stuff in H7 Departure Services Other					E2020		Stuff in H7	27/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff7	Other																						AY-Stuff
8	AB-Stuff8 AY-Stuff Stuff in H8 Departure Services Vendor Fee Other					E2021		Stuff in H8	28/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff8	Other																						AY-Stuff
9	AB-Stuff9 AY-Stuff Stuff in H9 Home Sale Title Coordination Fees Other					E2123		Stuff in H9	29/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff9	Other																						AY-Stuff
10	AB-Stuff10 AY-Stuff Stuff in H10 Home Sale (30/06/01) Inspection (AAAAA )					E2124		Stuff in H10	30/06/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff10	Other																						AY-Stuff
11	AB-Stuff11 AY-Stuff Stuff in H11 Health Insurance Payment Allowance (July 2001)					E2438		Stuff in H11	01/07/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff11	Other																						AY-Stuff
12	AD Stuff					E2002		Stuff in H12	02/07/2001			L-Stuff												X-Stuff		Z-Stuff	AAAAA	AB-Stuff12	Other	AD Stuff
WS1

Cell Formulas
Range	Formula
A2:A12	A2	=IF(AD2<>"",(AD2),AB2&" "&AY2&" "&H2&" "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOOKUP(F2,Sheet4!$A$2:$B$11),"'I'",TEXT(I2,"dd/mm/yy")),"'AC'",AC2),"'X'",X2&" "),"'L'",L2&""),"'Z'",Z2&" "),"'AA'",AA2&" "),"'Imy'",TEXT(I2,"mmmm")&" "&TEXT(I2,"yyyy") ))

MRXLMAY21.xlsm
	A	B
1	ENUM	Sentence
2	E2002	Househunting ('I') Airfare 'AC'
3	E2004	Househunting ('I') Lodging 'L'
4	E2007	Settling In Service 'X' 'Z'
5	E2008	Area Tour 'X'
6	E2010	Homefinding 'X'
7	E2020	Departure Services 'AC'
8	E2021	Departure Services Vendor Fee 'AC'
9	E2123	Home Sale Title Coordination Fees 'AC'
10	E2124	Home Sale ('I') Inspection ('AA')
11	E2438	Health Insurance Payment Allowance ('Imy')
Sheet4