Nested IF statements with a Search function

[Simone328]

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

 

[Fluff]

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]

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:

	A	B	C	K	L	M
Code	Result
Cigna Catalyst Pmt	CIGNA CATALYST
Adjustment to Month End Cash Balance for Wells Che	FICOBATCH-CM
ZBA Transfer	TRANSFER CREDIT
P/R	ADP
OCI	OCI
ach 8/18	US CUSTOMS
Reclass	NET
DIRECT DEPOSITS
ZBA Transfer	TRANSFER CREDIT

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]61[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TD="align: left"]L1: {=IF(K61="",B9,LOOKUP(2^15,SEARCH($A$2:$A$8,K61),$B$2:$B$8))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[Simone328]

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]

Hi Eric,

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

thank you Eric!

Regards,
Rich

 

[Fluff]

Cross posting is when you ask the same question on two or more different websites. I'd suggest that you read this https://www.excelguru.ca/content.php?184

 

[Scott Huish]

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
	A	B
1	Jack and Jill	TRUE
2	Old Mother Hubbard	FALSE
Sheet1

Cell Formulas
Range	Formula
B1	=ISNUMBER(SEARCH("Jack",A1))
B2	=ISNUMBER(SEARCH("Jack",A2))