Nested IF statements with a Search function

Simone328

New Member
Joined
Mar 23, 2017
Messages
6
Hello All,

I am hoping that someone can help me with this formula. It seems to breach when I add additional SEARCH functions in a nested IF statement.
When I added the last criteria to find the word "Reclass" I get a #Value Error

YES the source cell is TEXT. I did check it.
YES the word Reclass is in the cell in the sentence: Reclass CAD balance out of 113910

=IF(K61="Cigna Catalyst Pmt","CIGNA CATALYST",IF(K61="","DIRECT DEPOSITS",IF(K61="Adjustment to Month End Cash Balance for Wells Che","FICOBATCH-CM",IF(LEFT(K61,12)="ZBA Transfer","TRANSFER CREDIT",IF(SEARCH("P/R",K61),"ADP",IF(SEARCH("OCI",K61),"OCI",IF(SEARCH("ach 8/18",K61),"US CUSTOMS",IF(SEARCH("Reclass",K61),"NET"))))))))

Maybe there is a better way to Categorize items based on row "K"

THANK YOU!
Rich
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,774
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-fo...ted-if-statements-with-a-search-function.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 
Upvote 0

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,603
First, you get the #Value error if what you're looking for is not found. You should add an ISNUMBER function around all your SEARCH functions to avoid that:

...ISNUMBER(SEARCH("OCI",K61))...

Next, if you have Excel 365, you might want to look up the IFS function, it would make your formula much easier to read and maintain.

Finally, you might consider a table version that would simplify things, and make the formula easier to change:


ABCKLM
1CodeResult
2Cigna Catalyst PmtCIGNA CATALYST
3Adjustment to Month End Cash Balance for Wells CheFICOBATCH-CM
4ZBA TransferTRANSFER CREDIT
5P/RADP
6OCIOCI
7ach 8/18US CUSTOMS
8ReclassNET
9DIRECT DEPOSITS
61ZBA TransferTRANSFER CREDIT

<tbody>
</tbody>
Sheet3

Array Formulas
CellFormula
L1: {=IF(K61="",B9,LOOKUP(2^15,SEARCH($A$2:$A$8,K61),$B$2:$B$8))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0

Simone328

New Member
Joined
Mar 23, 2017
Messages
6
Hello if I did a cross post I do apologize, no idea what that is or how to not do it again

Regards,
Rich
 
Upvote 0

Simone328

New Member
Joined
Mar 23, 2017
Messages
6
Hi Eric,

why the ISUMBER in he formula ? I know that I am dealing with a text source.

thank you Eric!

Regards,
Rich
 
Upvote 0

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,961
Office Version
  1. 365
Platform
  1. Windows
Hi Eric,

why the ISUMBER in he formula ? I know that I am dealing with a text source.

thank you Eric!

Regards,
Rich

Because SEARCH returns a number at the place where it found the text. So, ISNUMBER check to see if a number was returned, and if so, the text was found. An error is not a number and therefore would return FALSE, meaning it's not found.


Excel 2010
AB
1Jack and JillTRUE
2Old Mother HubbardFALSE
Sheet1
Cell Formulas
RangeFormula
B1=ISNUMBER(SEARCH("Jack",A1))
B2=ISNUMBER(SEARCH("Jack",A2))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,187,109
Messages
5,961,638
Members
438,557
Latest member
Jadatutor

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