[bamma]

I need to create a formula in a single cell without further cells being
used for calculations.

I'm trying to return the date when my Available stock will run out based on a fortnightly forecast.

A B C D E F G
1 CODE Cover Avail 1-Sep 15-Sep 29-Sep 13-Oct
2 NET001 1/09/2008 64000 36000 0 0 48000

I put the formula in B2. Which attempts to return the date from the 1st row.

Using =INDEX(D1:G1,MATCH(C2,D2:G2,1)) gets me the result 13-Oct.

Problem then is that once I start included the rest of the forecast numbers
the MATCH only looks for the closing matching number.

Eg:
H1 = 27-Oct
H2 = 144,000
I1 = 10-Nov
I2 = 96,000

Change the Avail Number (C2) to 150,000

Using =INDEX(D1:I1,MATCH(C2,D2:I2,1)) gets me the result 10-Nov.
which is wrong - It should return 27-Oct because it is within that period
that my stock will run out.

Any suggestions?
Thanks.

[weejas]

You can't achieve this with Excel without making some preetty radical changes.

The MATCH function is working perfectly according to the Help file - according to the arguments supplied, it's looking for the largest value in lookup_array that is less than or equal to lookup_value. It does not perform any comparisons or other functions on the values within lookup_array.

I've managed to get the right answer (according to what you need) on the attached spreadsheet, by doing the following:

• Reversing the order of the dates and their forecast quantities
• Adding a row of calculated cells to get the running forecast (please note the semi-anchored formula)
• Changing the match_type arguement to -1

Hope this helps!

[bamma]

Thanks for your reply.

I did get an answer for it eventually.

Where $AM$3:$AY$3 contains my fortnightly forecast dates

Where $AM4:$AY4 contains my fortnightly forecast qty's

And $H4 is my available Stock.

{=INDEX($AM$3:$AY$3,MATCH(TRUE,SUBTOTAL(9,OFFSET($ AM4:$AY4,,,,COLUMN($AM4:$AY4)-MIN(COLUMN($AM4:$AY4))+1))>$H4,0))}

Ctrl + Shift + Enter (Array)

It works well.

Cheers,

Bam.