+ Reply to Thread
Results 1 to 11 of 11

Does list 2 values exist somewhere in the list 1 cell?

  1. #1
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Does list 2 values exist somewhere in the list 1 cell?

    Hi, I need to find out if any of the list 2 values exists somewhere in the list 1 cells. Below is an example .. The lists will really be many times bigger so I need a formula! The solution I need is a yes or a no against List 1 cells. Many thanks.

    Example:

    List 1
    A2 = NOPOS Ltd WFD REF 38476FH
    A3 = 2015095562 MCARE
    A4 = 045456AB NOPOS
    etc ..

    List 2

    A2 = 38476
    A3 = 45456
    A4 = 468281

    List 1 Solution ...

    B2 = YES
    B3 = NO
    B4 = YES
    Last edited by popskull; 12-17-2013 at 01:08 AM.

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Does list 2 values exist somewhere in the list 1 cell?

    Something like this? see the file

    Azumi
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Does list 2 values exist somewhere in the list 1 cell?

    Unfortunately not the solution Azumi. List 2 is a lookup list. So in your example cell C5 should be yes as 38476 in cell A5 exists in the lookup list 2. Any other solutions? Many thanks, Alex

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Does list 2 values exist somewhere in the list 1 cell?

    If you list 1 is in col. A and list 2 is in col. C, try this array formula and confirm it with Ctrl+Shift+Enter in B2 and drag down.
    Please Login or Register  to view this content.
    See the attachment. if the list 2 is not in col. C you can change the range in red in the formula.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Does list 2 values exist somewhere in the list 1 cell?

    Brilliant. Many thanks sktneer

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Does list 2 values exist somewhere in the list 1 cell?

    I need to find out if any of the list 2 values exists somewhere in the list 1 cells
    In this case a simple =COUNTIF($A$2:$A$9,"*"&B2&"*") does the job ( or have I misunderstood ?)

  7. #7
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Does list 2 values exist somewhere in the list 1 cell?

    Quote Originally Posted by Pepe Le Mokko View Post
    In this case a simple =COUNTIF($A$2:$A$9,"*"&B2&"*") does the job ( or have I misunderstood ?)
    Hi Pepe,the countif doesn't work for me. sktneer solution works for me though. The problem is I have a large List 1 that has cells in A which contains text and numbers in no particular format or pattern. I have another List 2 that contains numbers I need to find in List 1 cells. If any of the List 2 numbers is present somewhere in A2, A3, A4 etc of List 1 then that cell needs to be flagged as a YES... as per the example below. The Lists will be quite large and on separate sheets or files. I could be missing something though so if your solution works please post a file.

    List 1
    A2 = NOPOS Ltd WFD REF 38476FH
    A3 = 2015095562 MCARE
    A4 = 045456AB NOPOS
    etc ..

    List 2

    A2 = 38476
    A3 = 45456
    A4 = 468281

    List 1 Solution ...

    B2 = YES
    B3 = NO
    B4 = YES

  8. #8
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Does list 2 values exist somewhere in the list 1 cell?

    sktneer's solution works for numbers , but what if List 2 had "MCARE". What would the formula be to return List 1 B3 = YES in the above example. Also is it possible to instead of returning YES or NO to return the actual value in List 2, so B2 in List 1 solution = 38476. ?

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Does list 2 values exist somewhere in the list 1 cell?

    maybe like this
    note list 2 should be sorted by length smallest to largest just put =len(e2) in f2 fill down then sort both columns by col f
    this will make the formula look for the longest mat ch
    so if you had ab123 and ab1234
    and your look up list had
    1234
    123
    in that order
    both would match 123
    but if your lookup list is sorted
    123
    1234
    then 1234 would be tested first and match ab1234
    Attached Files Attached Files
    Last edited by martindwilson; 12-17-2013 at 10:17 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Registered User
    Join Date
    09-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Does list 2 values exist somewhere in the list 1 cell?

    Great work Martin, I didn't know lookup and search could be used that way , and without an array! May I ask what does 2^15 do?

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Does list 2 values exist somewhere in the list 1 cell?

    2^15 is actually just 32768
    its just a number bigger than anything search is likely to return

    =LOOKUP(2^15,SEARCH(B1:B4,A1))

    where

    a1= yyy fff xxx
    b1:b4 =
    ddd
    eee
    fff
    ggg


    =lookup(32768,search({"ddd";"eee";"fff","ggg"};"yyy fff xxxx")
    =lookup(32768,{#value!;#value!;5;#value!})
    which means fff was found starting char 5 of the string yyy fff xxxx
    lookup ignores errors like #value! and #DIV/0!
    so it returns the highest value less than 32768 that it can find in this case 5
    if you add the range as the result vector

    =lookup(32768,{#value!;#value!;5;#value!},{"ddd";"eee";"fff","ggg"})
    then it returns the value corresponding position where it is found that is pos 3 so returns fff
    Last edited by martindwilson; 12-18-2013 at 08:04 AM.

+ 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. [SOLVED] Formula to Flag names in list 1 that do not exist in list 2.
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-16-2013, 11:28 AM
  2. Replies: 0
    Last Post: 09-11-2013, 09:54 AM
  3. Replies: 10
    Last Post: 09-28-2012, 07:17 PM
  4. [SOLVED] adding a name to a list if it does not already exist
    By slaneues in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-25-2012, 10:33 AM
  5. List row numbers where cell values exist
    By maxald in forum Excel General
    Replies: 8
    Last Post: 06-07-2010, 07:45 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