LookUp (Date) Value from Forecast Data - From 2nd Sheet

[Bam]

Previously... I had in issue which was resolved by using the following.

Dates in the range D1:J1
Usage amounts in the range D2:J2
Stock on hand in C2

Entered as an array** :

=INDEX(D1:J1,MATCH(TRUE,SUBTOTAL(9,OFFSET(D2:J2,,,,COLUMN(D2:J2)-MIN(COLUMN(D2:J2))+1))>=C2,0))

Format as date

A result of #N/A means you will not run out of stock.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Thankyou (Biff)


My new problem is similar except that my forecast is on a seperate sheet
and I need to use a match/vlookup? formula to get the row numbers of where
the usage amounts are.

Sheetname = Data.

Dates in the range - Data!$G$1:$AA$1 (Constant)

Usage amounts in the range Data!$G$x:$AA$x
(where "x" = the row(s) number.)

I don't know the row number unless I use Match.
Eg: =MATCH(VALUE(A4),Data!A:A,0) - Which Returns "16" - Which is the
correct row number.

Is there a way of incorporating the Match formula into thearray??

Note: The Usage amount are spread over 2 rows.
Eg: Data!$G$2:$AA$3

Any help would be much appreciated.

 

[Spiky]

Yes, use INDIRECT.

INDIRECT("Data!$G$"&MATCH(VALUE(A4),Data!A:A,0)&":$AA
$"&MATCH(VALUE(A4),Data!A:A,0))

 

[Lori]

Try inserting x-2 into the row offset part of Biff's formula where x is the
result of your match formula.

To make it non-volatile, you could try replacing subtotal(...) by:
prob(D1:J1*{1;-1},index(D:J,x,)*{1;-1}+(D1:J1=D1)*{0;1},,D1:J1)

 

[Bam]

Lori, I'm not to sure what you mean?

Can you put it into the formula for me to test?

Thanks for your help.