VLOOKUP/INDEX/MATCH Function When Using Automation To Input Date/Time

Hello all. I currently have two sheets. The main one is where people make requests, and I need to calculate when a person 1. Starts the request, 2. When it's on the next stage, 3. When it is completed. So I set an automation to copy the row onto the second sheet when the status is changed to "Start", "Next Stage", "Finished". It'll automatically track when the Date/Time starts (using Modified Time). I want to copy the date/time onto the main sheet. When I use VLOOKUP or INDEX/MATCH is there a way it can check the other rows if the data is empty? I assume it's looking at the data in the 3rd row.

=VLOOKUP([Summary Description]@row , {Test - PMC Helper Range 7}, 4)

=INDEX({Test - PMC Helper Range 5}, MATCH([Summary Description]@row , {Test - PMC Helper Range 3}, 0)) + ""

Main Sheet

Second Sheet

Find more posts tagged with

Accepted answers

All comments

jjg279

Do you only want it to return when something is finished? Or for each task? Depending on what you are after you could make a helper column to gather all the dates in one column using a "JOIN([pmcStart]:[pmcFinish], "")" and then reference that column.

Or if you want to know if all three actions have been done (and your Sum Description is unique) you could use a "COUNTIF" to get the number of lines that items exists on.

Paul Newcome

Try switching to an INDEX/COLLECT instead to filter the INDEX results excluding blanks.

=INDEX(COLLECT({Test - PMC Helper Range 5}, {Test - PMC Helper Range 5}, @cell <> "", {Test - PMC Helper Range 3}, @cell = [Summary Description]@row), 1)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.