Nested IF statement work around

[2077delta]

I need to analyze a text string for various string combinations then substitute an alternate string depending on the search results. The following If statements provide the logic, but since Excel is limited to 7 nested If statements, nesting is out of the question. Is there a function in VBA or some other combination of formulas that can get the desired result? Oh, and by the way if none of these strings are found I want the result to be "Other".

if(FIND("LOCK",I8,1)>0,"Lock Box",
if(FIND("CR",I8,1)>0,"Cash Receipts",
if(FIND("CONF PR T",I8,1)>0,"Taxes",
if(FIND("CONF P/R",I8,1)>0,"Conf-Payroll",
if(FIND("NF",I8,1)>0,"Nations Fund",
if(FIND("PAYMENT TR",I8,1)>0,"AP",
if(FIND("PR TX",I8,1)>0,"Taxes",
if(FIND("ZBA HRLY",I8,1)>0,"ZBA Hrly Payroll",
if(FIND("ZBA SALARY",I8,1)>0,"ZBA Salary Payroll",
if(FIND("401K",I8,1)>0,"401K",
if(FIND("FUI",I8,1)>0,"Taxes",
if(FIND("SUI",I8,1)>0,"Taxes",
if(FIND("LOC INT",I8,1)>0,"LOC Interest",
if(FIND("WORKERS COMP",I8,1)>0,"Workers Comp",
if(FIND("MEDICAL",I8,1)>0,"Medical",
if(FIND("HELD CHECKS",I8,1)>0,"Held Checks",
if(FIND("LOAN PAYMENT",I8,1)>0,"LOC Payment",
if(FIND("LOAN ADVANCE",I8,1)>0,"LOC Advance","Other"))))))))))))))))))


Thanks

 

[Aladin Akyurek]

Create a table of associations as shown below...

Book2
	A	B	C	D	E	F	G	H	I	J
1	Table of Associations
2	@	Other
3	401K	401K
4	CONF P/R	Conf-Payroll
5	CONF PR T	Taxes
6	CR	Cash Receipts
7	FUI	Taxes
8	HELD CHECKS	Held Checks							LOC INT	LOC Interest
9	LOAN ADVANCE	LOC Advance							DRAGON ALLEY	Other
10	LOAN PAYMENT	LOC Payment							NF	Nations Fund
11	LOC INT	LOC Interest							PR TX	Taxes
12	LOCK	Lock Box
13	MEDICAL	Medical
14	NF	Nations Fund
15	PAYMENT TR	AP
16	PR TX	Taxes
17	SUI	Taxes
18	WORKERS COMP	Workers Comp
19	ZBA HRLY	ZBA Hrly Payroll
20	ZBA SALARY	ZBA Salary Payroll
21
Sheet1

and invoke...

J8, copied down:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$20,"@"&I8),$B$2:$B$20)

Note the first record with @ and Other, which is added to the table of associations.

 

[barry houdini]

Hello Aladin,

I note that should I8 contain "CONF PR T" then formula will return "Nations Fund" because "NF" is contained within "CONF PR T".

I'd suggest that "NF" should be placed higher up the table than "CONF PR T"

 

[2077delta]

Thanks for the quick response, but Aladin could you please repost your response. It looks like the HTML code came across with your answer and it's sort of difficult to sift through for the actual answer.

Once again thanks.

 

[Aladin Akyurek]

Hello Aladin,

I note that should I8 contain "CONF PR T" then formula will return "Nations Fund" because "NF" is contained within "CONF PR T".

I'd suggest that "NF" should be placed higher up the table than "CONF PR T"

Right. I thought I could get around the #N/A problem with the "@" record... As the NF case shows, that creates an insoluble problem.

The OP has to accept #N/A for DRAGON ALLEY. So I propose to change the first record to:

A2:

=""

B2: Other

Then to invoke...

J8:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$A$2:$A$20&" "," "&I8&" "),$B$2:$B$20)

which is a robust idiom.

Of course #N/A can be avoided with morefunc's SETV/GETV pair if so desired. Thus:

Book2
	A	B	C	D	E	F	G	H	I	J
1	Table of Associations
2		Other
3	401K	401K
4	CONF P/R	Conf-Payroll
5	CONF PR T	Taxes
6	CR	Cash Receipts
7	FUI	Taxes
8	HELD CHECKS	Held Checks							LOC INT	LOC Interest
9	LOAN ADVANCE	LOC Advance							DRAGON ALLEY	#N/A
10	LOAN PAYMENT	LOC Payment							NF	Nations Fund
11	LOC INT	LOC Interest							PR TX	Taxes
12	LOCK	Lock Box							CONF P/R	Conf-Payroll
13	MEDICAL	Medical								Other
14	NF	Nations Fund
15	PAYMENT TR	AP
16	PR TX	Taxes
17	SUI	Taxes
18	WORKERS COMP	Workers Comp
19	ZBA HRLY	ZBA Hrly Payroll
20	ZBA SALARY	ZBA Salary Payroll
21
Sheet1

 

[Aladin Akyurek]

Thanks for the quick response, but Aladin could you please repost your response. It looks like the HTML code came across with your answer and it's sort of difficult to sift through for the actual answer.

Once again thanks.

You need to enable HTML in your profile.

 

[2077delta]

Thanks , it works great. Could you please explain what the first argument in the lookup formula means. I understand the rest, but this is a little beyond me.

 

[Aladin Akyurek]

Thanks , it works great. Could you please explain what the first argument in the lookup formula means. I understand the rest, but this is a little beyond me.

LOOKUP(9.99999999999999E+307,{9,#VALUE!,4,#N/A},{"X","V","Z","Y"})

==> Z, which corresponds to the last numeric value in {9,#VALUE!,4,#N/A}.

See for more: http://tinyurl.com/83b2x