Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking up in a list (2007)

    I am having trouble with a formula to look in a list for a particular string.

    I have a long list of names sorted in one column. In another column, I have a short list of names. I wold like to have some formula in the cell next to each name in the short list that will compere the short list name to the long list and tell me if that name exists in the long list. More accurately, I need to know when it does not exist in the long list.

    I thought VLOOKUP might do it but I have not had success.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking up in a list (2007)

    Let's say that the long list is in A1:A1000, and that the short list is in D110.
    Enter the following formula in E1 (next to the first cell of the short list):
    <code>
    =ISERROR(MATCH(D1,$A$1:$A$1000,0))
    </code>
    Fill down from E1 to E10. The formulas will return TRUE if the value does not occur in the long list, FALSE otherwise. If you'd prefer to display a text only if the value does not occur in the long list, use a variation like this:
    <code>
    =IF(ISERROR(MATCH(D1,$A$1:$A$1000,0)),"Missing","" )</code>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Looking up in a list (2007)

    Hi Don

    Does thi do what you want:

    =IF(ISNA(MATCH(D2,$B$2:$B$6,0)),"Not in list","In list")
    Jerry

  4. #4
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking up in a list (2007)

    Thanks to both of you, they both do just what I needed.

Posting Permissions

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