Returning a date from another sheet using two criteria

Frank Falco

I have two sheets, one being a Related sheet that has three columns:

Number, Label, Date

Then there is a Main Sheet that also has

Number, Label, Date (plus other columns)

This is simulating a One-to-Many relationship as the Related Sheet can have multiple entries for each Number which I want to collect into multiple columns on one row into the main sheet dependant on what the Label is in the Related Sheet.

I want to search for the matching number and label values and return the date from the Related sheet

Is there a way to match multiple columns and return a value (Date) other than using

=JOIN(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"))

which returns the Date as a string and then I have to have a string column to return that into and then use a formula to convert the string to a date, such as the formula:

=IF(NOT(ISBLANK(DateStr@row)), DATE(VALUE("20" + RIGHT(DateStr@row, 2)), VALUE(MID(DateStr@row, 4, 2)), VALUE(LEFT(BDStr@row, 2))))

Doing this sometime causes errors temporarily (until a reload and save) in the Main sheet due to the Australian date format being DD/MM/YYYY and there being instances where the date is returned in the US format MM/DD/YYYY which is invalid for DD greater than 12. Might have something to do with hosting on US servers.

Not to mention it is a lot of unnecessary calculating and extra columns which is cumbersome.

Genevieve P.

Hi @Frank Falco

What about using an INDEX(COLLECT instead of JOIN? JOIN will turn the Date into string and will add together multiple dates. INDEX will find one value and can be returned into a Date type of column... although if there's more than one match it will only find the first one.

The structure is almost exactly the same:

=INDEX(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"), 1)

It just needs the 1 at the end to indicate it should pull the first match back. Would this work for you?

Cheers,

Genevieve

Genevieve P.

Hi @Frank Falco

What about using an INDEX(COLLECT instead of JOIN? JOIN will turn the Date into string and will add together multiple dates. INDEX will find one value and can be returned into a Date type of column... although if there's more than one match it will only find the first one.

The structure is almost exactly the same:

=INDEX(COLLECT({Related Sheet Date}, {Related Sheet Num}, [Main Sheet Number]@row, {Related Sheet Label}, "Label 1"), 1)

It just needs the 1 at the end to indicate it should pull the first match back. Would this work for you?

Cheers,

Genevieve

Frank Falco

Thank you @Genevieve P. that worked nicely.

Genevieve P.

Wonderful! Thanks for letting me know 🙂

reece.buckner

@Genevieve P.

I found this thread and have a related issue that I cannot figure out. I have a formula very similar to what you proposed, but it is returning an "INVALID VALUE". Please help me figure out what I am doing wrong.

=INDEX(COLLECT({Project Timeline Range 1}, {Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row), 1)

I am trying to pull the date from the Project Timeline sheet which corresponds to both the task of Develop Submittal Drawings, and which has the matching Project Name. My Range which I am trying to pull from is the Start Date column, which is pulling from the Start column via =Start@row, because I read that you cannot lookup information in a column which is used in dependencies.

Genevieve P.

Hi @reece.buckner

Good news: you can actually reference a Date column in another sheet in a formula, even if that column is used with dependencies. I believe the limitation you're describing is being able to put a formula in that column (versus referencing/looking at it from a separate formula).

Can you double check that all of your columns are Date type of columns? (The one the formula is placed in, as well as what it's looking at).

The next thing to check is to ensure there's a match with your criteria. I see your project name is set to "Test Test" - is that the correct project name?

You can ensure there's a match by using a COUNTIFS formula with your same criteria:

=COUNTIFS({Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row))

If you get 0, then your INDEX(COLLECT formula would error as there's no match. You can wrap an IFERROR function around it to return that text instead of an error:

=IFERROR(INDEX(COLLECT({Project Timeline Range 1}, {Project Timeline Range 2}, [Project Info]@row, {Project Timeline Range 3}, [Project Name]@row), 1), "No Match")

Let me know if this helped!

Cheers,

Genevieve