Thread: Index (?) And/Or Arrays (2003)

1. Index (?) And/Or Arrays (2003)

I am attempting to estimate sales data for the upcoming year based on a prior years actuals. My delima is that in column A, I have the months of November 06-October 07 in text (MM/YYYY), in that order. In column B, I have the actual sales figures for those months. I want to use those figures, rounded to the nearest -3, and insert in column D (Column C has the months of January-December 2008 listed in calendar year order). The problem is column C starts with January 2008, whereas column A starts with November 2006. Is there a way with the Index and/or array function(s) to put a formula in each of the twelve cells in column D that will look and see if the months in columns A and C match, then insert the contents of column B into column D, rounded to the nearest -3?

2. Re: Index (?) And/Or Arrays (2003)

See the sample file...

3. Re: Index (?) And/Or Arrays (2003)

Thanks, Rudi. A slight twist to my facts. The November 06-October 07 months (text) are in column B; the actual sales data are in column C, and the January 08-December 08 month names are in column Y, and the place where I want the data to be placed is column Z. What do I need to change in your formula to make it work?

4. Re: Index (?) And/Or Arrays (2003)

OK...

In column Z put this formula :
=ROUND(IF(MATCH(MONTH(Y1:Y12),MONTH(B1:B12),0),IND IRECT("C"&MATCH(MONTH(Y1:Y12),MONTH(B1:B12),0))),-3)

It is an array function, so you must confirm entry with CTRL+SHIFT+ENTER. Also just edit the row values as mine only goes up to row 12!

6. Re: Index (?) And/Or Arrays (2003)

Hmmm...the formula is putting the data into the wrong row. I have put in a row function to pull the current row number and getting results, but still testing the results.
=ROUND(IF(MATCH(MONTH(Y160:Y171),MONTH(B160:B171), 0),INDIRECT("C"&ROW())),-3)

7. Re: Index (?) And/Or Arrays (2003)

Thanks. Interesting...

8. Re: Index (?) And/Or Arrays (2003)

Or this array formula in Z160:Z171 (confirm with Ctrl+Shift+Enter):

=ROUND(INDEX(C160:C171,MATCH(MONTH(Y160:Y171),MONT H(B160:B171),0)),-3)

9. Re: Index (?) And/Or Arrays (2003)

Sorry...not working as ROW() pulls from current row except if I say ROW()+2 <img src=/S/wink.gif border=0 alt=wink width=15 height=15> [sly]

10. Re: Index (?) And/Or Arrays (2003)

You got it!!! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

11. Re: Index (?) And/Or Arrays (2003)

Hans,
I don't know what I'm doing wrong, but here is the file with your suggested formula. For some reason, it is returning the same value for all the cells!
Thanks again.

12. Re: Index (?) And/Or Arrays (2003)

You need to select Z160:Z171, enter the formula and press Ctrl+Shift+Enter, not enter it in one cell as an array and then copy down.

13. Re: Index (?) And/Or Arrays (2003)

Thanks, Rory. I have never had to enter an array like that before! It had always worked in the past by entering it once, and then copying down. I wonder what makes this one different?

14. Re: Index (?) And/Or Arrays (2003)

Since the formula contains MONTH(Y160:Y171), it refers to Y160:Y171 as a whole - this is one of the powerful things you can do with array formulas: enter a single formula in a range of cells.
Note: you cannot edit such a formula for individual cells, only for the entire range. Neither can you delete it from individual cells, only from the entire range.

It would also have been possible to use this array formula in Z160:

=ROUND(INDEX(\$C\$160:\$C\$171,MATCH(MONTH(Y160),MONTH (\$B\$160:\$B\$171),0)),-3)

and fill down to Z171.

15. Re: Index (?) And/Or Arrays (2003)

This one actually returns an array of values, as opposed to working on arrays but only returning one value. That's why you have to enter it in multiple cells simultaneously.

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•