index match formula not working

David Friesen

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

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

David 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 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

 

David 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 Williams

You are welcome.

Craig