How to auto-advance an array formula

[AussieExcelUser]

I have the following array formula which I entered into a column and down 200 cells, however the last MATCH reference (shown as G8 in the formula) did not auto advance and therefore every cell in the column returns the same value.

=INDEX(Fcast_Volume,MATCH(1,(Fcast_Location=$G$7)*(Fcast_Product=G8),0))

How do I get an array formula to copy down and auto-advance the same as if I had entered a non-array formula and simply copied it down using copy & paste?

BTW, the formula is designed to (I hope) return a value from a named range (Fcast_Volume) when the cells on the same row in range Fcast_Location and named range Fcast_Product match what is in fixed cell reference $G$7 and variable cell reference G8 respectively.

[Bryan Hessey]

I have the following array formula which I entered into a column and down 200 cells, however the last MATCH reference (shown as G8 in the formula) did not auto advance and therefore every cell in the column returns the same value.

=INDEX(Fcast_Volume,MATCH(1,(Fcast_Location=$G$7)*(Fcast_Product=G8),0))

How do I get an array formula to copy down and auto-advance the same as if I had entered a non-array formula and simply copied it down using copy & paste?

BTW, the formula is designed to (I hope) return a value from a named range (Fcast_Volume) when the cells on the same row in range Fcast_Location and named range Fcast_Product match what is in fixed cell reference $G$7 and variable cell reference G8 respectively.

HI,

It advances for me, did you press F9 ?

(of course, I get the error for having no FCast etc, but the formula advances G8 - G9 - G 10 etc)

Did you formula drag the small + ?

---

[AussieExcelUser]

G'day Bryan,

I did hit F9 to re-calculate but to no avail.

I have continued to muck around with this and reckon my issue lies with how I am entering the array formula.

I have just got it to work in another column but am unsure as to how I did

What is the keystroke sequence I need to use? Starting from the first cell selected and the formula entered into the formula section of the toolbar - where do I go from there?

As always, your help is most appreciated.

[AussieExcelUser]

D'oh. Don't worry, I have figured it out and have refrained from punching myself and my laptop

Thanks for the response anyway Bryan.

Cheers,

Grant.

[Bryan Hessey]

G'day Bryan,

I did hit F9 to re-calculate but to no avail.

I have continued to muck around with this and reckon my issue lies with how I am entering the array formula.

I have just got it to work in another column but am unsure as to how I did

What is the keystroke sequence I need to use? Starting from the first cell selected and the formula entered into the formula section of the toolbar - where do I go from there?

As always, your help is most appreciated.

Hi,

Nothing I can think of, the column not set to Text format (the formula would show, not the result)

Just enter the formula, Copy, select a range and Paste

It should should work. F9 is for if you have 'Calculation' set to Manual, normally set to Auto.


The column not corrupt? - pre-used?

no other idea springs to mind.

--

[Bryan Hessey]

D'oh. Don't worry, I have figured it out and have refrained from punching myself and my laptop

Thanks for the response anyway Bryan.

Cheers,

Grant.

? text ?


---

[AussieExcelUser]

Nope, just incorrect sequence of entering and copying down. You gave me a hint with dragging down to copy.

I was selecting the entire range, copying, hitting F2, then Ctrl, Shift & Enter.

Once I entered into the first cell, hit Ctrl, Shift & Enter and then copied down using the "+" it worked fine.

[Bryan Hessey]

Nope, just incorrect sequence of entering and copying down. You gave me a hint with dragging down to copy.

I was selecting the entire range, copying, hitting F2, then Ctrl, Shift & Enter.

Once I entered into the first cell, hit Ctrl, Shift & Enter and then copied down using the "+" it worked fine.

Ok - good to see it's finally working for you and thanks for the response.

---