Replace OFFSET with INDEX in complex array formula

[PhilW_34]

Hi All,

I've been working at this for the better part of this morning. No matter what I try, I cannot make this formula work without using the volatile OFFSET formulas nested in this formula. So, I'm coming for help.

What I'm doing is looking at inventory and the comparing it to monthly forecasts and returning the month in the header when it will run out. Any suggestions? Thanks in advance.

Header is in Row 3
Data is in row 4. Forecasts start with JAN in F4 an go through Q4.

=IF(MIN(IF(SUBTOTAL(9,OFFSET($F4,0,0,1,COLUMN($F4:$Q4)-COLUMN(F4)+1))>=E4,COLUMN($F4:$Q4)-COLUMN(F4)+1))=0,"Overstocked",OFFSET($F$3,,MIN(IF(SUBTOTAL(9,OFFSET($F4,0,0,1,COLUMN($F4:$Q4)-COLUMN(F4)+1))>=E4,COLUMN($F4:$Q4)-COLUMN(F4)+0))))

I then hit Shift+Ctrl+Enter to make this an array formula.

Thanks again,

Phil

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Explain an example of the result you expect

 

[XOR LX]

Hi,

Array formula**:

=IFERROR(INDEX(F3:Q3,MATCH(1,N(MMULT(F4:Q4,N(COLUMN(F4:Q4)>=TRANSPOSE(COLUMN(F4:Q4))))>=E4),0)),"Overstocked")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

 

[PhilW_34]

Hi,

Array formula**:

=IFERROR(INDEX(F3:Q3,MATCH(1,N(MMULT(F4:Q4,N(COLUMN(F4:Q4)>=TRANSPOSE(COLUMN(F4:Q4))))>=E4),0)),"Overstocked")

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

This appears to do the trick. Thank you very much. I have not used MMULT before; so, I have something new I can look up and learn from. This has been driving me nuts. Thanks so much for your time and helpful solution.

Phil

 

[XOR LX]

No worries. Glad to help!

Cheers