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

Rachael B · 06/25/24

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)

DKazatsky2 · 06/25/24

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 · 06/25/24

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 · 06/25/24

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 · 06/25/24

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

That worked!!! Thank you!!!

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

Answers

Help Article Resources

Categories