Using IF(SEARCH) for ~ and Regular IF in Nested Formula

[jmdiva]

I'm trying to do a nested IF statement but also include an IF(SEARCH) in the same formula.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]$15[/TD]
[TD]$1000[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]$25[/TD]
[TD]$100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]~$30[/TD]
[TD]$75[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



For example, Column A will occasionally include ~ for dollar amounts that are estimates. In Column C I'm trying to solve for the following criteria:

=IF(A2="","D&I"
=IF(A2>B2,"Raise","No Action")
=IF(SEARCH("~~",A2),"N/A"

I tried combining them like this: =IF(SEARCH("~~",A2),"N/A",IF(A2="","D&I",IF(A2>B2,"Raise","No Action")))

But every time I get an error. Some help on this would be greatly appreciated!

 

[Eric W]

Welcome to the Board!

Maybe:

=IF(A2="","D&I",IFERROR(IF(A2+0>B2,"Raise","No Action"),"N/A"))

 

[jmdiva]

Thanks Eric, that worked!

 

[Eric W]

Glad to help!

I used a trick (the IFERROR and the +0) to avoid using the SEARCH, but you can also fix up your version with the ISNUMBER function:

=IF(ISNUMBER(SEARCH("~~",A2)),"N/A",IF(A2="","D&I",IF(A2>B2,"Raise","No Action")))