Pull Date from Separate Sheet That Matches 2 Row Criteria

Amanda Brixius

So the company I work for has a system of T-Minus dates based on round (easier to understand with screenshot below), since we have repeating tasks round over round. This system really confuses people and it's a PITA to have to constantly look up dates based on these criteria. I have a feeling this is a fairly simple formula for someone familiar with it, but my experience is fairly limited.

Basically, I'd like to input a formula in the Baseline Start Column that will pull info from another sheet based on the Round and T-Minus Start columns.

I've created two separate sheets, one for start and one for finish, so I assume both formulas will effectively be the same. I just want the formula to find the matching Round row and T-Minus Column and pull the correct date for me. In this instance, Round 1 (RD01) and T-38 should return 4/14/22

I'm not sure if I should be using MATCH or COLLECT within INDEX. I've never used any of these before and I'm having a hard time wrapping my head around how to accomplish this.

Thanks!

Paul Newcome

It looks like your "t minus" are incrementing by week (7 days). In that case I would suggest a basic INDEX/MATCH to grab from the -45 column and the add days based on >>((-45 - [T-Minus Start]@row) * 7)<<.

=INDEX({Reference Sheet -45 Column}, MATCH(Round@row, {Reference Sheet Round Column}, 0)) + ((-45 - [T-Minus Start]@row) * 7)

Andrée Starå

Hi @Amanda Brixius

I hope you're well and safe!

You’d use and INDEX/COLLECT combination.

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

It looks like your "t minus" are incrementing by week (7 days). In that case I would suggest a basic INDEX/MATCH to grab from the -45 column and the add days based on >>((-45 - [T-Minus Start]@row) * 7)<<.

=INDEX({Reference Sheet -45 Column}, MATCH(Round@row, {Reference Sheet Round Column}, 0)) + ((-45 - [T-Minus Start]@row) * 7)

Amanda Brixius

Hi Paul, thanks for the response!

I ended up figuring it out myself after a bit of trial and error. This is what I got...

=INDEX({Index Range}, MATCH(Round@row, {1st Column Range}), MATCH([T-Minus Start]@row, {1st Row Range}))

From there I made a second formula that references my T-Minus Finish dates (which is in it's own separate document), instead of start. It's pretty straight forward now that I understand it, but this is my first time using INDEX so it took awhile to wrap my head around it

Paul Newcome

Happy to help. 👍️