Look up a project ID and return a date from another sheet

Hello,

I am trying to pull in the start date from another sheet. I have tried both Vlookup and Index, but have not been successful. Here is the formula that got me the closest, but I believe the formatting of the date isn't working. Date format is currently MM/DD/YYYY, I have also tried Month Day, Year.

Originating Data Set

Designation

Here are the references I am using

=INDEX({Master Folder Tracker Reference 1}, MATCH([Project ID]@row, {Master Folder Tracker Reference 1}, 0), 9)

=VLOOKUP([Project ID]@row, {Master Folder Tracker Reference 1}, 9, false)

Find more posts tagged with

Accepted answers

All comments

DKazatsky2

Hi @Rachael B,

It looks like you are using the same cross-sheet reference in your formula for the INDEX and the MATCH - these should be different. The reference in INDEX is what you want returned, the reference in MATCH is what to check against,

Try this formula: =INDEX({Start Date}, MATCH([Project ID]@row, {Project ID}, 0))

{Start Date} = cross-sheet reference to the start date column in "Master Folder Tracker Do Not Edit"

{Project ID} = cross-sheet reference to the Project ID column in "Master Folder Tracker Do Not Edit"

Hope this helps,

Dave

Rachael B

https://community.smartsheet.com/discussion/comment/442340#Comment_442340

Hello,

I just tried this and am still getting #Invalid Column Value results for the dates

DKazatsky2

Hi @Rachael B,

Ensure all date columns are set to "Date" type in column properties. Also, try wrapping the formula in an IFERROR and see if that helps.

=IFERROR(INDEX({Start Date}, MATCH([Project ID]@row, {Project ID}, 0)), "")

Rachael B

https://community.smartsheet.com/discussion/comment/442343#Comment_442343

That worked!!! Thank you!!!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.