Trying to return the date if a certain row task is marked Complete

Bill in Ohio

After spending some time learning about a bunch of different things in Smartsheet that aren't helping with my problem, I thought I should just ask my question. I am missing something small I think.

I have tasks in my projects that need to be completed after it is implemented. I want to capture the "Execution Complete" task (basically when the users have their system in place) for reporting purposes.

I can find the Task Name that equals "Execution Complete":

=INDEX(Status:Status, MATCH("Execution Complete", [Task Name]:[Task Name], 0))

This does return "Complete", so far so good.

What I'm trying to do is basically an IF statement that it the Status = "Complete", return the value in End Date. If it isn't, then return "Ongoing".

=IF(INDEX([Status]:[Status], MATCH("Execution Complete",[Task Name]:[Task Name],0)) = "Complete", VLOOKUP("Execution Complete", [Task Name]:End Date, 5, false), "Ongoing")

This gives #UNPARSEABLE. Since there is only one row that will have "Execution Complete" in the Task Name column, I thought I would be able to search through the rows, until it finds the right row, and then evaluate the Status and return either the End Date or "Ongoing."

I'm storing the value in the sheet summary, to avoid using a helper sheets because I am using this same formula on each project to display in a report.

Does someone have an idea of where I went wrong?

MichaelTCA

Hello @Bill in Ohio

I would replace the vlookup with the index/match function and change the lookup column to the end date since everything else is the same.

Also, the part that specifies the lookup range in the vlookup function should be [Task Name]:[End Date]. End Date should be in brackets. This alone might fix your issue, otherwise try what I mentioned above.

MichaelTCA

Hello @Bill in Ohio

I would replace the vlookup with the index/match function and change the lookup column to the end date since everything else is the same.

Also, the part that specifies the lookup range in the vlookup function should be [Task Name]:[End Date]. End Date should be in brackets. This alone might fix your issue, otherwise try what I mentioned above.

Bill in Ohio

Thank you very much @MichaelITCA , I will try this today. Sorry for the late appreciation - I'm working on a lot of exciting things that are keeping me busy. (Probably like most people are here, but I do appreciate the feedback!)

Bill in Ohio

Well back to searching. It works when the status isn't complete, but after modifying it to:

=IF(INDEX([Status]:[Status], MATCH("Execution Complete", [Task Name]:[Task Name], 1)) = "Complete", INDEX([End Date]:[End Date], MATCH("Execution Complete", [Task Name]:[Task Name], 0)), "Ongoing")

I get the #NO MATCH error.

MichaelTCA

@Bill in Ohio

In your MATCH function, your only specifying 1 value instead of an ambiguous variable like [Task Name]@row. You can use the AND function if you're looking for the complete status and execution complete task.

You could try this:

=IF(AND(Index/match="Execution Complete",index/match="Complete"), index/match end dates, "ongoing")