Formula Needed: Formula needed in a Date field of the Sheet Summary (v-lookup almost?)

rshoniber

I need help creating a formula that will link the "Key Date" from the Project Intake Sheet to the launched project within the Sheet Summary field. Essentially, it needs to be able to link back to the Project Intake Sheet and search for the coordinating Project Name to identify the row it will need to pull the data from, then identify the specific column it needs to pull from. In my mind, its very similar to a v-lookup in Excel but I can't seem to master this. 

Example: Formula will be entered in the Project Plan Sheet Summary for "Project Example Alpha" for the custom field of "Key Date 2". It should link back to the "Project Intake Sheet - PMO for HR (Example)" and search for the Project Name of "Project Example Alpha" and the Column of "Key Date 2". 

If I can do this, then we only need to keep the Project Intake Sheet up to date with the dates, as they change, and all the coordinating launched projects will update automatically in the Sheet Summary, thus all the Due Dates will automatically update as well. 

Pics attached as reference.

Project Intake Sheet.JPG

Sheet Summary Pic.JPG

jmyzk_cloudsmart_jp

Hi @rshoniber

Suppose you have a "Project Name" field in your Sheet Summary.

If you use VLOOKLUP, the formula for "Key Date #2" should look like this;

=VLOOKUP([Project Name]#, {Project Intake Sheet's range beginning the Project Name Column and end at the Kye info 3 column}, the relative position of the Key Date 2 in the range, i.e., 5)

=VLOOKUP([Project Name]#, {Intake Sheet Range Project Name: Key Info 3}, 5)

As the VLOOKUP formula fails if the order of the column included in the above range changes, the best practice is to use INDEX(MATCH() formulas, which should look like this:😀

=INDEX({range of Key Date #2 at the intake sheet}, MATCH([Project Name]#, {range Project Name at the intake sheet},0))

=INDEX({Key Date 2}, MATCH([Project Name]#, {Project Name}, 0))

For details, please check the demo dashboard below.

https://app.smartsheet.com/b/publish?EQBCT=21cfa6bb68c344888a3561ac56284d0d