+ Reply to Thread
Results 1 to 9 of 9

Lookup Formula To Check Exceptions

  1. #1
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Lookup Formula To Check Exceptions

    Hi

    I'm struggling to finalise a formula.

    Students write various tests in the class. If they fail any they fail overall.
    I have a table with list of students and test results.

    I'm looking for a formula that checks against each students results and if there is a 'FAIL' the formula returns fail.
    If they're all 'pass' then it shows a pass.

    I tried a vlookup, but it only finds the students first test results. I want it to check against each test result.

    See attached demo.

    Any help is greatly appreciated!
    Kind Regards
    Alex
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Lookup Formula To Check Exceptions

    Hi Camel, hoe gaan dit in Joe'ies?

    Try this...
    =IF(COUNTIFS($A$2:$A$18,F2,$C$2:$C$18,FALSE)>0,"Fail","pass")
    copied down
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Lookup Formula To Check Exceptions

    Hi

    JHB is good. :-)

    I've imported the formula and applied it to my use case but it's not working. It's working with original demo but for the life of me, I can't see why it won't apply in my use case.

    What have I missed?
    =IF(COUNTIFS(MajOverview[URL],H3,MajOverview[Spam In Anchor],"SPAM")>0,"SPAM","CLEAN")
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Lookup Formula To Check Exceptions

    Ensure "SPAM" in column F doesnt have any spaces before/after the word or hidden characters. Same with URL columns...it all needs to be apples to apples or not match is found. IE: "SPAM" is not the same thing as " SPAM " as far as Excel sees it.

    You can try and simplify the formula to start, do just the COUNTIFS, and see what the counts are to ensure they are the correct counts, then wrap it in the IF.

    Another testing tool is using the filters in your table, copy the wording from say H and paste 1 of them into the filter in your table...if it doesnt find a match they arent the same exact string. Other tools to troubleshoot include the LEN function and EXACT function. You can use them to test if 2 cells have the same length of characters and/or are the exact same thing.

    Hope this helps

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Lookup Formula To Check Exceptions

    It works in your last demo file. Perhaps include wildcards
    =IF(COUNTIFS(MajOverview[URL],H3,MajOverview[Spam In Anchor],"*SPAM*")>0,"SPAM","CLEAN")
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  6. #6
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Lookup Formula To Check Exceptions

    Quote Originally Posted by xlnitwit View Post
    It works in your last demo file. Perhaps include wildcards
    =IF(COUNTIFS(MajOverview[URL],H3,MajOverview[Spam In Anchor],"*SPAM*")>0,"SPAM","CLEAN")
    Just be aware that wildcards add a margin of error and especially in larger workbooks can drastically slow down calculations

    In my opinion, you are better off doing what is called "normalizing your data". This concept is essentially what I shared. Make sure you are comparing apples to apples. Eliminate excess spaces, non print characters, typos and make sure data types match (numbers are both numeric or both text). There are many tools that help do this quickly.

    Formulas:
    CLEAN(TRIM(
    LEN
    EXACT

    features:
    Find & Replace (find " " and replace with ""...aka find a space and replace with nothing)
    Text to columns (in some cases, as it can be used to format a whole column to numeric or text when other methods do not work)
    Evaluate formula (Formula | Evaluate)

    Hope this helps

  7. #7
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Lookup Formula To Check Exceptions

    Indeed, but if the wildcards work then you know there is an issue with the data in that column, which can be fixed. If they don't, something else is at work.

    As I said, the formulas in the second demo file work for me and do not look like the picture posted in the same post, so I wonder if it is a simple matter of manual calculation being set.

  8. #8
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Lookup Formula To Check Exceptions

    Quote Originally Posted by xlnitwit View Post
    Indeed, but if the wildcards work then you know there is an issue with the data in that column, which can be fixed. If they don't, something else is at work.

    As I said, the formulas in the second demo file work for me and do not look like the picture posted in the same post, so I wonder if it is a simple matter of manual calculation being set.
    Ah excellent point, forgot about manual calculation as well. Make sure your set to automatic or that you recalc as needed if your on manual.

  9. #9
    Registered User
    Join Date
    01-13-2010
    Location
    Jhb
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Lookup Formula To Check Exceptions

    Quick feedback before I close thread:
    1) Wildcards are awesome! I'd simplified my use case and these were perfect.
    2) also - manual calculations when I took screenshot.
    3) also - data cleaning was nb! (it's a bigger spreadsheet)

    Thanks for all the help. Rep added.

+ 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] Two check lookup
    By sungen99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-21-2016, 01:35 PM
  2. Formula: % for each month and Number count for Exceptions
    By mydragonstalents in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-19-2013, 05:58 PM
  3. Exceptions to a formula
    By ButtersEFC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 07:20 AM
  4. Help please! two dimentional lookup with exceptions
    By turbo600hp in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-16-2012, 04:45 PM
  5. Need sumif formula with exceptions
    By Kfain in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 05-31-2012, 02:25 PM
  6. Lookup table with exceptions?
    By gavster in forum Excel General
    Replies: 7
    Last Post: 04-07-2009, 08:32 AM
  7. need check two worksheets to lookup a value
    By Clay in forum Excel General
    Replies: 2
    Last Post: 01-05-2005, 05:06 AM

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