Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: If Value X exists, say yes or no

  1. #1
    Board Regular
    Join Date
    Sep 2010
    Location
    Melbourne
    Posts
    184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool If Value X exists, say yes or no

    Hi guys,
    I need to create a method of going down column b, and looking at the value in column A, and seeing if it exists in a table I have on another sheet. If Value A exists, then I want ColB to say yes or no.

    It seems like it should be so simple, but its killing me!


  2. #2
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    what column is the table on the second sheet in
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  3. #3
    Board Regular
    Join Date
    Sep 2010
    Location
    Melbourne
    Posts
    184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    5, or E
    starting in row 2


  4. #4
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    Code:
    =IF(ISNA(MATCH(A1,$F$1:$F$100,0)),"No","Yes")
    fill down

    you will need to change the F1:F100 reference to your new sheet
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  5. #5
    Board Regular
    Join Date
    Sep 2010
    Location
    Melbourne
    Posts
    184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    Thank you Diddi,
    You da man!
    I was trying so many combinations of match, find, index, vlookup... i was just in circles...

  6. #6
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    so did it rain yesterday
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  7. #7
    Board Regular
    Join Date
    Sep 2010
    Location
    Melbourne
    Posts
    184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    Apparently, but I don't go out much...

  8. #8
    Board Regular diddi's Avatar
    Join Date
    May 2004
    Location
    Shepparton, Australia
    Posts
    2,550
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    ooh thats a bit geeky
    The more you learn, the more you realise how little you know. Excel 2003 and 2010 / Win7
    Code:
    Sub HintForGoodResults( )
        If yourcode Is indented Then
            "it is easier to for everyone to understand" AND "others are more likely to give assistance"
        Else
            "errors are much harder to find" OR "others dont bother"
        End If
    End Sub

  9. #9
    New Member
    Join Date
    Jul 2015
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If Value X exists, say yes or no

    Quote Originally Posted by diddi View Post
    Code:
    =IF(ISNA(MATCH(A1,$F$1:$F$100,0)),"No","Yes")
    fill down

    you will need to change the F1:F100 reference to your new sheet
    Bumping an old post because this worked for me.

    I edited the result to =IF(ISNA(MATCH(E2,E:E,0)),"Not Found","Value found on row " & MATCH(E2,E:E,0)) to match my range

    I then added traditional CF (I'm using Excel 2003) to E:E column with FormulaIs =E3=E4 and chose a light yellow for the cell colour; which highlights any duplicates of the above rows.


    Many thanks, your gift keeps on giving...

Some videos you may like

User Tag List

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
  •