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.

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!

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.