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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,645
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>
 

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
 

Simone328

New Member
Joined
Mar 23, 2017
Messages
6

ADVERTISEMENT

Hi Eric,

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

thank you Eric!

Regards,
Rich
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Jack and Jill</td><td style="text-align: right;;">TRUE</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Old Mother Hubbard</td><td style="text-align: right;;">FALSE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=ISNUMBER(<font color="Blue">SEARCH(<font color="Red">"Jack",A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=ISNUMBER(<font color="Blue">SEARCH(<font color="Red">"Jack",A2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,504
Messages
5,602,047
Members
414,497
Latest member
guitarmanz

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