+ Reply to Thread
Results 1 to 7 of 7

Help with IF/OR/AND? Formula

  1. #1
    Registered User
    Join Date
    06-23-2009
    Location
    Herts
    MS-Off Ver
    Excel 2007
    Posts
    7

    Help with IF/OR/AND? Formula

    Hi guys,

    I am currently running a spreadsheet which has an IF statement which also uses ISNA & MATCH. The formula is working fine bit i would like to adapt it slightly to include a couple of other statements - I thought it would be quite a simple one at first with a nested IF statement but am having difficulties with it - wondering if any of you can help?

    I have attached a simplified version of my sheet.

    I would like column C to display TBC or YES depending on the conditions.

    As it is the column will display YES if the name in column A matches one of the names in column E.

    However i would also like C to display YES if the Role column (B) is populated (regardless of whether the corresponding name in A is in E) with any text UNLESS it says "Hunter".

    So.....

    If A is in the the range E:E then C = YES OR if B = any text (with the exception of "Hunter") then C = YES.

    If A is NOT in the range E:E AND B = blank or "Hunter" then C = TBC.

    I hope that makes sense!

    In the sheet attached the results for C2 to C21 should come out as :

    C2 YES
    C3 YES
    C4 YES
    C5 TBC
    C6 YES
    C7 YES
    C8 YES
    C9 TBC
    C10 YES
    C11 YES
    C12 TBC
    C13 TBC
    C14 TBC
    C15 TBC
    C16 YES
    C17 TBC
    C18 TBC
    C19 YES
    C20 YES
    C21 TBC

    Any help would be most appreciated!

    Thanks in advance
    Richard
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Help with IF/OR/AND? Formula

    Hi,

    an attempt in C2 and down:

    Please Login or Register  to view this content.
    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,142

    Re: Help with IF/OR/AND? Formula

    hi bellpiero, you may try:
    Please Login or Register  to view this content.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    06-23-2009
    Location
    Herts
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Help with IF/OR/AND? Formula

    Hi both,

    Thanks very much for your responses. They have almost solved the issue but there is one small discrepancy. I may not have been too clear so apologies for that!

    If the name in col A is in the E:E range then i would like the Status to be YES regardless of whether B was "Hunter" or not.

    So the two formulas provided work except for when a name is added that is in the names database and is "Hunter" in col B. An example of this would be adding the following into row 22:

    A22: Mark, B22: Hunter.

    I would need C22 to become YES but with these formulas it is TBC. Basically if the name is in the E:E range it should override anything else and result as YES.

    Regards

  5. #5
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Help with IF/OR/AND? Formula

    Ciao Bellpiero,

    attached an example.

    Maybe:

    Please Login or Register  to view this content.
    Regards
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-23-2009
    Location
    Herts
    MS-Off Ver
    Excel 2007
    Posts
    7

    Re: Help with IF/OR/AND? Formula

    Canapone,

    Thank you! Works perfectly!

  7. #7
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: Help with IF/OR/AND? Formula

    Ciao,

    thanks for your nice feedback.

    Greetings from a humid and sticky Firenze

+ 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