IF AND INDEX MATCH to pull the correct row based on criteria


Hello all,

I am struggling to come up with the correct formula to pull the right information between master sheet and personal tracker and I wondered if you can help. My logic for creating the formulas is not the very best.

What I'm essentially trying to do is pull the right start/end date and budgeted hours from the master tracker. Because there are multiple rows with the same project code, a simple index/match just won't do. Can you please advise how to pull from this?

I created a hidden column in the personal tracker with the name of the assignee and basically, if the row in the master tracker has SOW of certain value and the name matches, then pull the date from that.

The same goes for #of budgeted hours (personal tracker) vs MO hours (master sheet).

Please see below for the layout of personal tracker (pic #1) and master tracker (pic #2).

The master tracker is composed of parent and children rows as seen in the second picture (not sure if that is important to mention).


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!