index match formula not working

David Friesen
David Friesen ✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, I am using the below index match formula which is not working. The MFG Rev 18 area range is a "contact list" column type, and the column used for the formula is a Text type.   I assume the formula is not working due to the type, is there another way around this? 

=INDEX({Mfg Rev 18 area}, MATCH([Start Date]3, {start date range}, 0))

Thank you, 

Dave

Comments

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    What feedback are you getting?

    #INCORRECT ARGUMENT SET 

    #CONTACT COLUMN DOES NOT SUPPORT FORMULAS

    #NO MATCH

    or something else?

    If the formula resides in a Contact List type column, you will get the message that they can't have formulas.

    The formula WILL return the name (not email address or full contact info) from the {Mfg Rev 18 area} range, if it finds it.

    The range sizes need to be the same.

    Also note that your formula will return the first match it finds, which is OK, but is sometimes an odd thing for a Date field. No inconceivable, but odd.

    I hope this helps.

    Craig

  • The error is #NO MATCH.  The range sizes are the same. There is only one date that matches the reference in the formula.  Formula is in Text/Number type.  I changed the dates so both were not a formula but a date only.   It's a bugger anything I should look at? 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Yes.

    There are two types of date related columns. 

    One is the Date column. The other is a Date/Time column. The second are the two columns associated with Project Settings when Dependencies are enabled and the two system columns.

    The image below has color coded match formulas. 

    The first tries to find a DATE in a range of DATE/TIME

    =MATCH(SD@row, Start$1:Start$4, 0) 

    The second tries to find a DATE/TIME in a range of DATE

     =MATCH(Start@row, SD$1:SD$4, 0)

    both fail because it is like apples and tomatoes, both red, both fruit, but not the same.

    The third forces the DATE/TIME to be a DATE, so it finds a match.

    =MATCH(DATEONLY(Start@row), SD$1:SD$4, 0)

    I don't know a way to force the range to converted from DATE/TIME to DATE without creating a new column to do so.

    Maybe that helps?

    Craig

     

    DateMatch.png

  • I removed the predecessor in the project settings and it worked.  Fortunately there was no need for predecessors on this sheet.  

    Craig, thanks for your invaluable help and quick replies. 

  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    You are welcome.

    Craig

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!