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)