Results 1 to 5 of 5
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
    503

    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
    65,780

    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
    503

    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
    503

    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

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