+ Reply to Thread
Results 1 to 10 of 10

IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

Hybrid View

  1. 07-10-2015, 10:48 AM #1
    joshuarobbins
    joshuarobbins is offline
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Hello everyone,

    I am trying to create a function for a spreadsheet at work, and my function continues to return the "false" value, even if it should be true. Below is the function I have so far:

    =IF(AND(ISNUMBER(SEARCH("VA",J3)),(K3=810),(K3=1050),(K3=1080),(N3<=0)),"Increase","Needs Review")
    What I would like is for the function to return the value true, if cell K contains any of the 3 numbers (810, 1050, or 1080). Is this a posibility? I have also tried K3=810,1050,1080, and that has not worked. Thank you in advance for the help.

    Joshua Robbins
    Reply With QuoteRegister To Reply
  2. 07-10-2015, 11:03 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Hi there. try this out:

    =IF(AND(ISNUMBER(SEARCH("VA",J3)),OR(K3=810,K3=1050,K3=1080),(N3<=0)),"Increase","Needs Review")
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 07-10-2015, 11:06 AM #3
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,082

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    =if(or(k3=810,k3=1050,k3=1080),true,false)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  4. 07-10-2015, 12:13 PM #4
    joshuarobbins
    joshuarobbins is offline
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Alright, that seemed to work! So, I was separating the number values by commas only, when it should have been the entire cell value. That makes sense.
    Reply With QuoteRegister To Reply
  5. 07-10-2015, 12:28 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    Reply With QuoteRegister To Reply
  6. 07-10-2015, 01:53 PM #6
    joshuarobbins
    joshuarobbins is offline
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Hello everyone,

    I have come across an issue for the same function that I was typing earlier. Excel is now giving me an error that I've "entered too many arguments for this function". What does that mean, and is there an easy way to fix this? Below is my function.

    =IF(AND(ISNUMBER(SEARCH("VA",J3)),OR(K3=810,K3=1050,K3=1080,L3=810,L3=1050,L3=1080),(N3<=0)),"Increase",IF(AND(ISNUMBER(SEARCH("VA",J3)),OR(K3=810,K3=1050,K3=1080,L3=810,L3=1050,L3=1080),(N3>0)),"Increase w/ Fee",IF(AND(ISNUMBER(SEARCH("VA",J3),(N3<=0)),"Submit",IF(AND(ISNUMBER(SEARCH("VA",J3)),(N3>0)),"Submit w/ Fee",IF(AND(ISNUMBER(SEARCH("Yes",M3)),(N3<=0)),"Submit",IF(AND(ISNUMBER(SEARCH("Yes",M3)),(N3>0)),"Submit w/ Fee","Needs Review")))))))
    Any help would be greatly appreciated.
    Reply With QuoteRegister To Reply
  7. 07-10-2015, 02:12 PM #7
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    It means that you have the close parenthese in the wrong spot(s) For example,

    =IF(ISNUMBER(Search(A1, B1), "Yes", "No"))
    The ISNUMBER should only have 1 argument. In the formula above, it has 3; SEARCH(A1,B1), "Yes" and "No"
    You need to match up your parentheses and see where one is misplaced.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  8. 07-10-2015, 03:35 PM #8
    joshuarobbins
    joshuarobbins is offline
    Registered User
    Join Date
    05-20-2015
    Location
    United States
    MS-Off Ver
    Microsoft Office 2013
    Posts
    36

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Alright, I see what you mean and I was able to make my correction. Now, if I would like to add this function to an existing macro, how would I convert it to the R1C1 format?
    Reply With QuoteRegister To Reply
  9. 08-04-2019, 01:01 PM #9
    DFBrandy
    DFBrandy is offline
    Registered User
    Join Date
    08-04-2019
    Location
    Haymarket, Virginia
    MS-Off Ver
    10
    Posts
    3

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    I have a list of data that consist of narrative description of accidents. I am trying to search this data for specific words "rib", "face" and "longwall face." I have tried the =ISNUMBER(SEARCH(" rib "," &K4&""")) formula and the =IF(OR(K5="rib",K5="face",K5="longwall face"),TRUE,FALSE) and nothing works. These formulas return FALSE when the answer should be TRUE. Why? I have tried to paste the data as text and general format still I get FALSE as an answer. How can I correct this problem?
    Reply With QuoteRegister To Reply
  10. 08-05-2019, 02:53 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: IF AND ISNUMBER SEARCH Function. Keeps returning false value even if true.

    Hi. Please take a moment to re-read rule 4, which you agreed to stick to, a few moments ago. Then start your OWN thread. To improve your chances of getting a quick answer...

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] in this TRUE OR FALSE function, i want desired name instead of display true or false
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-15-2013, 06:44 PM
  2. Function to search Column for a range of values and return TRUE or FALSE
    By BamBamMoneyBags in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-05-2013, 05:59 AM
  3. Logical test in IF function returning TRUE when it should be FALSE
    By lvjeff in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-28-2012, 05:13 PM
  4. IF(ISNUMBER(SEARCH(Find Text, Within Text)),True, False)
    By lucraft in forum Excel General
    Replies: 3
    Last Post: 11-21-2011, 06:44 AM
  5. Search for 2 true arguments and return true or false
    By David in forum Excel General
    Replies: 3
    Last Post: 07-15-2006, 05:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1