index match formula not working

david friesendavid friesen ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions
07/31/18 Edited 12/09/19

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

Popular Tags:

Comments

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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

  • david friesendavid friesen ✭✭✭✭✭

    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 WilliamsJ. Craig Williams Top Contributor

    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([email protected], Start$1:Start$4, 0) 

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

     =MATCH([email protected], 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([email protected]), 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

  • david friesendavid friesen ✭✭✭✭✭

    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 WilliamsJ. Craig Williams Top Contributor

    You are welcome.

    Craig

Sign In or Register to comment.