+ Reply to Thread
Results 1 to 7 of 7

Finding items in column which are NOT in a given list

  1. #1
    Registered User
    Join Date
    05-19-2007
    Location
    Chicago, IL
    Posts
    10

    Question Finding items in column which are NOT in a given list

    Hi All,

    I am trying to create a lookup formula to either find the items which are NOT in a given list... or if it's a lot easier to simply say 'Yes', there are items existing which are not from your list.

    Example
    Let's say I have column A with 1000 rows. Almost every entry will be either 'Apple', 'Pear', or 'Peach' and that is what's expected. However, if there is a row that has something else like 'Banana' or 'Grape', I need to know. Note that this column of data is not sorted and can't be because it's already sorted by a different column.

    So, in this example, I'd like the formula to return the unexpected entries(Preferred) or at least return "Unexpected entries found..."

    Thanks for any help on this!

  2. #2
    Registered User
    Join Date
    06-28-2007
    Posts
    69
    This formula should work using VLOOKUP. I attached an example that might help as well.

    =IF(ISERROR(VLOOKUP(A2,$J$2:$J$4,1,FALSE)),"Unexpected entries found","")
    Attached Files Attached Files

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Finding items in column which are NOT in a given list

    Try something like this example:

    A2:A1000 contains the list of values.

    D1:D3 contains
    Apple
    Pear
    Peach

    This formula (in sections for readability) tests if ALL non-blank
    items match an item from the D1:D3 list:
    Please Login or Register  to view this content.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    another way similar to Ron's approach...

    Please Login or Register  to view this content.

    where C1:C2 is the list of valid items and A1:A5 is your original list to look in....

    this formula needs to be confirmed with CTRL+SHIFT+ENTER not just Enter.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Finding items in column which are NOT in a given list

    Another thought....

    After using a formula that only indicates that there are invalid items
    in the list, perhaps some Conditional Formatting could be used to flag
    those items.

    Using the example I posted previously with items in Col_A and the
    list of valid items in D1:D3

    Select A2:A1000, with A2 as the active cell.

    From the Excel Main Menu
    <format><conditional formatting>
    Condition_1
    Formula is: =AND(A2<>"",ISNA(MATCH(A2,$D$1:$D$3,0)))
    Click the [Format] button and set the highlight color.
    Click [OK] to finish

    All invalid items will have that format.

    Is that something you can work with?

  6. #6
    Registered User
    Join Date
    05-19-2007
    Location
    Chicago, IL
    Posts
    10
    Quote Originally Posted by mpquin
    This formula should work using VLOOKUP. I attached an example that might help as well.
    Thanks. The problem I'll have with this approach is that I'd have to know beforehand the values that I'm not expecting...so this really won't work for me. I would need the formula to indicate on it's own items that are NOT Apple/Peach/Pear... Know what I mean?

  7. #7
    Registered User
    Join Date
    05-19-2007
    Location
    Chicago, IL
    Posts
    10

    Thumbs up Fantastic... thank you

    Ron/NBVC,

    I'm consistently amazed at the excellent knowledge of the folks on this forum. Fantastic. This is working beautifully and flexible enough to indicate something is not in my expected list.

    Thanks for your help!

    Russ

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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