Struggling with referencing a date formula from 2 different reference sheets

Todd Lozo

Hello,

In a nutshell, I'm trying to pull the date from 2 different reference sheets, with specified criteria for each. I want the most recent date from either sheet to be pulled, or if the date on one sheet is missing but the other sheet has a date, then it should pull from the sheet with the date.

Here is the formula I came up with (apologies for the length). When I run this I get an "invalid argument set" error:

=IF(AND([Turbine Serial Number]@row = INDEX({NAM 2023 MCE Job List Range 1}, MATCH([Turbine Serial Number]@row, {NAM 2023 MCE Job List Range 2}, 0)),

[Component Out Manufacturer]@row = "SKF"), INDEX({NAM 2023 MCE Job List Range 3}, MATCH([Turbine Serial Number]@row, {NAM 2023 MCE Job List Range 2}, 0)),

IF(AND([Turbine Serial Number]@row = INDEX({2023 Global MCE Job List Range 1}, MATCH([Turbine Serial Number]@row, {2023 Global MCE Job List Range 2}, 0)),

[Component Out]@row = "Main Bearing"), INDEX({2023 Global MCE Job List Range 3}, MATCH([Turbine Serial Number]@row, {2023 Global MCE Job List Range 2}, 0)),

IF(AND([Turbine Serial Number]@row = INDEX({2023 Global MCE Job List Range 1}, MATCH([Turbine Serial Number]@row, {2023 Global MCE Job List Range 2}, 0)),

[Second Component Out]@row = "Main Bearing"), INDEX({2023 Global MCE Job List Range 3}, MATCH([Turbine Serial Number]@row, {2023 Global MCE Job List Range 2}, 0)), "")))

• {NAM 2023 MCE Job List Range 1}: 'Turbine Serial Number' column in 'NAM 2023 MCE Job List' reference sheet
• {NAM 2023 MCE Job List Range 2}: 'Component Out Manufacturer' column in 'NAM 2023 MCE Job List' reference sheet
• {NAM 2023 MCE Job List Range 3}: 'RTS' column in 'NAM 2023 MCE Job List' reference sheet
• {2023 Global MCE Job List Range 1}: 'Turbine Serial Number' column in '2023 Global MCE Job List' reference sheet
• {2023 Global MCE Job List Range 2}: 'Component Out' and 'Second Component Out' columns in '2023 Global MCE Job List' reference sheet
• {2023 Global MCE Job List Range 3}: 'Actual RTS' column in '2023 Global MCE Job List' reference sheet

Any insights into this would be helpful. Thanks!

Genevieve P.

Hi @Todd Lozo

I would suggest using the MAX Function here!

For example:

=MAX(date 1, date 2)

or:

=MAX(first formula, second formula)

Blank cells are seen as dates "in the past" so it will automatically default to the cell that has a date.

For example, you could do something like this:

=MAX(INDEX({Date Column Sheet 1}, MATCH(Criteria@row, {Criteria Column Sheet 1}, 0)), INDEX({Date Column Sheet 2}, MATCH(Criteria@row, {Criteria Column Sheet 2}, 0)))

If you have more criteria to add in other than just the one to match, you could use INDEX(COLLECT instead. Here's an article with more information.

Let me know if this makes sense and works for you!

Cheers,

Genevieve