Column Formula to mimic HLOOKUP?

Julie@WD

Hi, Gurus!

I have a sheet that calculates 2 values based on whether the calculated columns contain an "X" in Row 2:

#Actual formula is: =SUMIF([Week 1]$2:[Week 300]$2, "X", [Week 1]@row:[Week 300]@row) [has "X"]

#Forecast formula is: =SUMIF($[Week 1]$2:$[Week 300]$2, "", $[Week 1]@row:$[Week 300]@row) [no "X"]

I need to convert this to a column formula, but I have to rewrite it to remove the specific row reference since column formulas do not support that. I'm stuck & I'm sure the answer is simple but eluding me at the moment.

In the above screen shot the data in the white rows is physically deleted & replaced weekly. The formulas in light blue must copy down automatically (hence the need for a column formula). Data is week by week total #hours worked. Past weeks are marked with an "X" to reflect actual hours, all others are considered "forecast".

I appreciate any help you can provide!

James Keuning

It seems to me that the quickest way is to use a set of helper columns to return the relevant value if the cell equals X, and another set of columns to return the value if if there is no X, and then use those columns for the SUM formulas.

But this [Week 1]$2:[Week 300]$2 - does that mean that this data has 300 week columns? Because if I am telling you to make 600 more column, that is not what I want to tell you to do. But the alterative requires you to pivot the data (long and skinny vs short and fat) and that's a whole other thing.

Julie@WD

Hi, James! Thanks for your reply! Unfortunately, you arrived at the same conclusion I did. Yes, my sheet has 300 week columns (this deployment type is typically multiple years in motion).

I'd like to explore your idea to go the "long and skinny" route - I had to INDEX(MATCH) the Weeks into a long, skinny list for another reason & there's nothing to restrict me from adding onto that framework. Can you tell me more about that idea?

Thanks again for your reply!! I look forward to your next one...

Julie