index match formula not working

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

Find more posts tagged with

Manufacturing

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

A multiple SUMIF function
My current formula is below. I would like to update this so that I only get a total of those that have been Quoted and have a timeframe data of 2025. How do I add this extra filter into the formula? =SUMIF(Status:Status, "Quoted", [Est. GP]:[Est. GP])
Missing column in Report if I use a formula
I'm not able to "view by" a field that I have in my sheets - Quarters (Q1, Q2, Q3, Q4) - in my report. I think it is because I have used a formula to calculate the quarters in my sheets. Does anyone know a way to get a formula based field on the dropdown for view by in my report?
SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…