+ Reply to Thread
Results 1 to 6 of 6

If a cells value is included in a list then cell should be zero

  1. #1
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    72

    If a cells value is included in a list then cell should be zero

    I am building a report and I have several checks I need the formula to do;

    1st - Look up code and if matching then = zero,
    2nd - If the code doesnt match then look up a second code in a list a if found then = Zero
    3rd - If neither of the above is true then multiply by X.

    I have added an example to make it more clear, I had thought of using the 'IF' formula but this may not be the best. I have made an attempt to show mu thinking in the cell highlighted in yellow.

    Any help is appreciated.

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: If a cells value is included in a list then cell should be zero

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    cheers

  3. #3
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    72

    Re: If a cells value is included in a list then cell should be zero

    Thank you very much! this has worked on my worksheet.

  4. #4
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    72

    Re: If a cells value is included in a list then cell should be zero

    Hi Gue2013,

    Thanks again as I said it is working but I was wondering if you could explain the logic to me for future use, obliviously understand the first section checking UNK and the final section doing the multiplication.

    However for the middle section that includes the 'ISNUMBER' and "MATCH', how exactly does that work? One worry I have is in my actually work the list will include both text and number in the cell so do you think this could cause errors later on? does it only work when checking a list of numbers?

  5. #5
    Valued Forum Contributor
    Join Date
    01-14-2013
    Location
    Austria
    MS-Off Ver
    2016 / 2019
    Posts
    339

    Re: If a cells value is included in a list then cell should be zero

    Match() :
    The MATCH function returns a numeric value (index of the value of an array).
    If the MATCH function does not find a match, it will return a #N/A error.

    Isnumeric(): returns true or false, depending on the content

    therefore:
    - if match finds something --> returns a value --> TRUE
    - if match does not find a value --> returns #N/A (not a value) --> FALSE

    there is no problem if the list contains values and characters!


  6. #6
    Registered User
    Join Date
    01-08-2020
    Location
    Toronto
    MS-Off Ver
    2010
    Posts
    72

    Re: If a cells value is included in a list then cell should be zero

    OK thanks, I understand. Thanks again for your help :D

+ 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. detect number of cells are included in a merged cell ?
    By engnajjar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2019, 06:12 AM
  2. Replies: 2
    Last Post: 05-25-2016, 12:46 PM
  3. [SOLVED] Using AutoFilter Criteria1 for list of NOT included
    By fpperryaz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-29-2016, 06:59 PM
  4. [SOLVED] Marking if one of a pair included in list
    By Cunner in forum Excel General
    Replies: 8
    Last Post: 04-09-2014, 04:48 AM
  5. List of standard variables already included in excel
    By tmox1130 in forum Excel General
    Replies: 1
    Last Post: 11-30-2012, 02:10 PM
  6. Replies: 0
    Last Post: 12-14-2011, 09:29 AM
  7. Countif included within a list?
    By di22y in forum Excel General
    Replies: 3
    Last Post: 09-04-2009, 08:44 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