Help with ISNUMBER

[wellen]

I have a spreadsheet with a list of titles such as this one in A2
[TABLE="width: 493"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CLT Referrals May 2017

I have a formula working on that cell:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}</style>=IF(ISNUMBER(SEARCH(Referrals,A2)), "Yes", "No")

the formula is returning no, when it should be returning yes.

It returns no whether I have the word Referrals in A2 or not.

Can you tell me what I'm doing wrong?

If it makes a difference, I'm the Excel for Mac 2011.

Thank you for any help you can give me!

Wellen
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

If you are searching for the word Referrals, then it needs to be enclosed in double-quotes (all literal text needs to be enclosed in double-quotes). Otherwise, it thinks Referrals is an object (function, range, variable, etc).

So try:

[COLOR=#333333]=IF(ISNUMBER(SEARCH("Referrals",A2)), "Yes", "No")[/COLOR]

 

[dreid1011]

Welcome to the forum.

You should probably try searching for the word with quotes around it. Like so:

SEARCH("Referrals",A2)

Otherwise, you are probably getting an error from the Search function but ISNUMBER is returning False because the error value is not a number.

 

[Rick Rothstein]

I have a spreadsheet with a list of titles such as this one in A2
[TABLE="width: 493"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CLT Referrals May 2017

I have a formula working on that cell:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}</style>=IF(ISNUMBER(SEARCH(Referrals,A2)), "Yes", "No")

the formula is returning no, when it should be returning yes.

It returns no whether I have the word Referrals in A2 or not.

Can you tell me what I'm doing wrong?

If it makes a difference, I'm the Excel for Mac 2011.

Thank you for any help you can give me!

Wellen
[/TD]
[/TR]
</tbody>[/TABLE]

You need to put quotes around the word "Referrals" otherwise Excel thinks it is a Defined Name.

 

[Roxxien]

Welcome on the forum

=IF(ISNUMBER(SEARCH("Referrals",A2)), "Yes", "No")

You need to have a string as your first argument in a search function.

 

[wellen]

Oh My Gosh! I can't believe I was being so stupid. Thank you!

May I ask for your indulgence once more?

If I wanted in that column to not only report whether the cell contained referral, but also resend, I know I need an If statement, but I'm struggling after that. Could you help me with that?

 

[Roxxien]

=IF(OR(ISNUMBER(SEARCH("Referrals",A2)),ISNUMBER(SEARCH("Resend",A2))) "Yes", "No")

This could be a solution.

 

[Joe4]

If looking for BOTH:

[COLOR=#333333]=IF(AND(ISNUMBER(SEARCH("Referrals",A2)),[/COLOR]ISNUMBER(SEARCH("Resend",A2))), "Yes", "No")

If looking for one or the other:

[COLOR=#333333]=IF(OR(ISNUMBER(SEARCH("Referrals",A2)),[/COLOR]ISNUMBER(SEARCH("Resend",A2))), "Yes", "No")

 

[wellen]

Perfect! Thank you so much! You're so smart and so fast!

I'm really grateful.

Wellen

 

[Rick Rothstein]

If looking for BOTH:

[COLOR=#333333]=IF(AND(ISNUMBER(SEARCH("Referrals",A2)),[/COLOR]ISNUMBER(SEARCH("Resend",A2))), "Yes", "No")

If looking for one or the other:

[COLOR=#333333]=IF(OR(ISNUMBER(SEARCH("Referrals",A2)),[/COLOR]ISNUMBER(SEARCH("Resend",A2))), "Yes", "No")

You can shorten those a bit...

One or the other
--------------------------------
=IF(OR(ISNUMBER(SEARCH({"Referrals","Resend"},A2))),"Yes", "No")

Both
--------------------------------
=IF(AND(ISNUMBER(SEARCH({"Referrals","Resend"},A2))),"Yes", "No")