Nested IF statement work around

2077delta

Board Regular
Joined
Feb 17, 2002
Messages
243
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Create a table of associations as shown below...
Book2
ABCDEFGHIJ
1Table of Associations
2@Other
3401K401K
4CONF P/RConf-Payroll
5CONF PR TTaxes
6CRCash Receipts
7FUITaxes
8HELD CHECKSHeld ChecksLOC INTLOC Interest
9LOAN ADVANCELOC AdvanceDRAGON ALLEYOther
10LOAN PAYMENTLOC PaymentNFNations Fund
11LOC INTLOC InterestPR TXTaxes
12LOCKLock Box
13MEDICALMedical
14NFNations Fund
15PAYMENT TRAP
16PR TXTaxes
17SUITaxes
18WORKERS COMPWorkers Comp
19ZBA HRLYZBA Hrly Payroll
20ZBA SALARYZBA 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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

Board Regular
Joined
Feb 17, 2002
Messages
243
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
ABCDEFGHIJ
1Table of Associations
2 Other
3401K401K
4CONF P/RConf-Payroll
5CONF PR TTaxes
6CRCash Receipts
7FUITaxes
8HELD CHECKSHeld ChecksLOC INTLOC Interest
9LOAN ADVANCELOC AdvanceDRAGON ALLEY#N/A
10LOAN PAYMENTLOC PaymentNFNations Fund
11LOC INTLOC InterestPR TXTaxes
12LOCKLock BoxCONF P/RConf-Payroll
13MEDICALMedicalOther
14NFNations Fund
15PAYMENT TRAP
16PR TXTaxes
17SUITaxes
18WORKERS COMPWorkers Comp
19ZBA HRLYZBA Hrly Payroll
20ZBA SALARYZBA Salary Payroll
21
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Feb 17, 2002
Messages
243
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
 

Similar threads

Forum statistics

Threads
1,136,307
Messages
5,674,986
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top