Results 1 to 8 of 8
Like Tree1Likes
  • 1 Post By Aladin Akyurek

Find a text within a range of cells

This is a discussion on Find a text within a range of cells within the Excel Questions forums, part of the Question Forums category; Hi, A B joe b elena c tony bob c joe a tom i need a formula if joe b ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Find a text within a range of cells

    Hi,

    A B

    joe b elena c
    tony bob c
    joe a tom

    i need a formula if joe b exists in A1:B3 return "yes" other wise "NO"

    Thank you

    Gaftalik

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    72,821

    Default Re: Find a text within a range of cells

    =--(COUNTIF(A1:B3,E1)>0)

    where E1 houses a value like "joe b".

    which will give 1 if success, otherwise 0.
    1968rm likes this.

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Find a text within a range of cells

    Dear Aladin,

    Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

    in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

    Thank you ,
    Gaftalik

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Find a text within a range of cells

    Quote Originally Posted by gaftalik
    Dear Aladin,

    Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

    in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

    Thank you ,
    Gaftalik
    Aladin's formula gets you what you need. What you want would be --

    =IF(COUNTIF('Other Sheet'!A1:A50,A1),"EXISTS","NOT")

    where A1 in this sheet houses your target value such as PL small
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Find a text within a range of cells

    Yeah you was right , the first formula worked as well as the second one , sorry but it was my mistake, thanks to both of you !

    gaftalik

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    23

    Default Re: Find a text within a range of cells

    hey aladin,
    is there any solution if i would like to search a approx text from a text string... where should be spel mismatchs

  7. #7
    New Member
    Join Date
    Sep 2015
    Posts
    1

    Default Re: Find a text within a range of cells

    What about if you want to find a text string that is returned by a formula?

    i have a few cells with formulae of this ilk:

    if(a1<>a2*1.2,"boo boo here: vat error","")
    if(and(b1>0,b2>0,"boo boo here: can't both be true","")

    and i want to search the document for "boo boo" and return each one...

    so countif works fine, but that just counts them
    I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
    Can you put conditions inside the match function that discludes formulae
    for example (though this doesn't work):

    =match(and("boo boo*",<>",?boo boo"),a1:a999,0)

    then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

    I'll be quite impressed with the person who solves this.
    Cheers,
    thegurumonkey

  8. #8
    New Member
    Join Date
    Sep 2015
    Posts
    15

    Exclamation Re: Find a text within a range of cells

    Quote Originally Posted by thegurumonkey View Post
    What about if you want to find a text string that is returned by a formula?

    i have a few cells with formulae of this ilk:

    if(a1<>a2*1.2,"boo boo here: vat error","")
    if(and(b1>0,b2>0,"boo boo here: can't both be true","")

    and i want to search the document for "boo boo" and return each one...

    so countif works fine, but that just counts them
    I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
    Can you put conditions inside the match function that discludes formulae
    for example (though this doesn't work):

    =match(and("boo boo*",<>",?boo boo"),a1:a999,0)

    then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

    I'll be quite impressed with the person who solves this.
    Cheers,
    thegurumonkey

    what you are exactly looking for ?
    if you want the text to be printed then its not possible because if u select the range then it will return more than one value at that time how it can be placed in one cell think of it...

    cheers.
    Darshan

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com