matching entries, a tricky one
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: matching entries, a tricky one

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    My problem is such i have an entry in a colum "2690 - L 6MT1 REG" i want to be able to compare the first 4 digits with a number in another colum and return the value true or false depending on wither they are matched.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-26 03:52, drag-driver wrote:
    My problem is such i have an entry in a colum "2690 - L 6MT1 REG" i want to be able to compare the first 4 digits with a number in another colum and return the value true or false depending on wither they are matched.
    =ISNUMBER(MATCH(LEFT(A1,4)+0,B:B,0))

    Substitute the true range in B for B:B.

    Aladin

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This should do the trick

    =IF(LEFT(A1,4)+0=C1,TRUE)

    The LEFT(A1,4) extracts the first four characters.
    The +0, converts it to number format (LEFT produces text by default)
    C1 is where I put the four digit value by itself
    Then there's just a bog standard IF around it.

    Rgds
    AJ

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    How about

    =IF(COUNTIF($C$1:$C$600,LEFT(A1,4)),TRUE)

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    Or, indeed, with COUNTIF:

    =COUNTIF(B:B,LEFT(A1,4)+0)>0

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-26 04:18 ]

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
  •  

 

 
DMCA.com