How if and search function together put values false as 0 or blank instead of #value! in cell.

[pankajgrover]

Hi, I am a very normal home basic user . Looking for 1 little help . Here attached excel, In Worksheet Test, R4 displaying #value! how can i calculate if result is false, then put a value like 0 or blank instead of #value!.
Actually I am trying to matching name with cell name , if exists i want some value divide some cells otherwise if not match name, result should be zero or blank.
Please help me with the formula. Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(SEARCH($R$2,C4)),O4/Q4,"")

 

[pankajgrover]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(SEARCH($R$2,C4)),O4/Q4,"")

Its just wow, so quick Thanks so much to @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[pankajgrover]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(SEARCH($R$2,C4)),O4/Q4,"")

As i apply your formula, it gives value blank as i want. But cell fill green color instead of grey. I already put condition formatting in cells that if cell value more than 0 then Green if less than 0 or - then red color. But if blank after applied your formula how can i give color to Grey, because this cell has no value ? Thanks

 

[Fluff]

In that case change the "" at the end of the formula to 0

 

[pankajgrover]

In that case change the "" at the end of the formula to 0

Also i found another solution and that is i go conditional formatting and i select cells condition if cells have blank put color fill is "Grey" and it works with "" also. Thanks for your good support and i get the solution exactly i just wants.

 

[Fluff]

Glad you sorted it.

 

[pankajgrover]

Hi @Fluff here is screenshot attached. in Q5 i am trying to count words of C5, but when i drag fill it counts also blank value and fill 1 value . how can make it to as blank in Q5.
I am using this =LEN(TRIM(C5))-LEN(SUBSTITUTE(C5," ",""))+1 but it gives blank column value as 1 instead of blank. Thanks

 

[Fluff]

As this is a totally different question it needs a new thread. Thanks