+ Reply to Thread
Results 1 to 10 of 10

ISTEXT(SEARCH not returning expected result

  1. #1
    Registered User
    Join Date
    07-23-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    ISTEXT(SEARCH not returning expected result

    Hey forum,
    I've been nesting a couple of IF statements and using the ISTEXT(SEARCH as part of the AND condition in those IF statements but the result seems to be coming back as the opposite to what I'm expecting it to return.

    This is what I've been trying to get to work:
    =IF(AND(E5=Tracking!C33,ISTEXT(SEARCH(“L”,Tracking!D33))),E5,G5)

    Cell E5 does equal 'Tracking!C33' and the cell 'Tracking!D33' does contain an "L".

    If I'm reading it right (which apparently I don't seem to be haha), that would return the value of E5 yes?
    The issue I'm having is that it is returning the value of G5 instead when all the conditions in the AND statement are true.

    Can someone point out what I've got wrong please or offer some advice or a different way to do it?

    Many thanks in advance.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: ISTEXT(SEARCH not returning expected result

    Hi,

    Search() returns the position of one string in another string. Hence it will not be a Text value and therefore FALSE.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: ISTEXT(SEARCH not returning expected result

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: ISTEXT(SEARCH not returning expected result

    SEARCH will return a numeric value, relative to that text's position in a string
    =SEARCH(“L”,Tracking!D33)
    will return 2 if D33 contains slip, and will return an #VALUE error if it is not found - so ISTEXT(SEARCH(“L”,Tracking!D33)) will always return FALSE
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: ISTEXT(SEARCH not returning expected result

    As Trevor suggested, change TEXT to NUMBER

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,733

    Re: ISTEXT(SEARCH not returning expected result

    An alternative:

    =IF(AND(E5=Tracking!C33,COUNTIF(Tracking!D33,"*L*")>0),E5,G5)

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    07-23-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: ISTEXT(SEARCH not returning expected result

    Thanks everyone!
    I understand now that I can't use the SEARCH function in an IF AND statement as a confirmation of a character being in a cell, which is what I was trying to do and why it wasn't working haha

    Pete that alternative worked perfectly for what I'm trying to do, thanks very much for that fix!

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,463

    Re: ISTEXT(SEARCH not returning expected result

    I understand now that I can't use the SEARCH function
    Yes you can, see post #3. You need to use ISNUMBER, not ISTEXT.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: ISTEXT(SEARCH not returning expected result

    It wasnt the SEARCH that wasnt working - that part worked just fine. It was what you were trying to do if the SEARCH did find what you were looking for.
    As I said "SEARCH will return a numeric value, relative to that text's position in a string", so you needed to test for a number, not text

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: ISTEXT(SEARCH not returning expected result

    Here's another one...

    =IF(AND(E5=Tracking!C33,COUNT(SEARCH(“L”,Tracking!D33))),E5,G5)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. hyperlink help need code not returning expected result (partial)
    By AkaTrouble in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-04-2015, 11:21 AM
  2. If, And, isText? How best to construct my formulas to get desired result?
    By gachogavacho in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2013, 02:35 PM
  3. Replies: 4
    Last Post: 07-02-2013, 09:47 PM
  4. VLookup not returning expected result
    By tenscourts in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-20-2012, 10:04 AM
  5. COUNTIF not returning expected result
    By zpenacho in forum Excel General
    Replies: 3
    Last Post: 01-20-2012, 06:37 PM
  6. SUMIF - not returning expected result
    By Paul Sheppard in forum Excel General
    Replies: 4
    Last Post: 08-16-2011, 11:43 AM
  7. [SOLVED] Using sum(1/countif....) not returning expected result
    By Kent (thanks) in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-11-2006, 11:40 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1