RE: Copy array formulas
- From: Sheeloo <Click on my name above to get instructions for getting my email id>
- Date: Sat, 31 Jan 2009 13:48:01 -0800
Did you download my sample file?
Either mail or upload your file so that I can see what is wrong.
I simply copied and pasted the data in your post and it worked for me.
This is the formula I have
=INDEX(AAC, MATCH(B2&C2,Company&Type,0))
"Scott A" wrote:
I appreciate the very timely response..
I agree and almost understand everything with this formula. While I am no
expert, I am no beginner either. This makes it even harder to understand why
this isn't working with my actual data. I am still getting #N/A (which I
failed to mention in the original post). I have tried CTRL-C & CTRL-V,
dragging the bottom right corner, and CTRL-C and Paste Special Formula. All
without luck.
This absolutely boggles my mind. As there a particular cell format required
(i.e., text, general, etc)? I've also to see if there was an option that
needed to be enabled or disabled. I have no clue wha tthe problem is.
I appreciate your help.
"Sheeloo" wrote:
How are you copying?
I entered everything the way you described with the array formula in A2.
Then I copied A2 to A3:A5 and it worked.
Download the sample file from http://wikisend.com/download/553030/Sample.xls
You can go to A3 and press CTRL-D
or
Drag the handle from A2 down to A3:A5
or
Copy A2 and paste over A3:A5
Are you selecting the formula and then pasting it as text in other cells?
"Scott A" wrote:
I know the fix is easy, but I can't seem to copy my array formula. In sum, I
am trying to have a column check two other columns. Based on the returned
values, I expect my value. Following is sample data:
AAC Company Type
Coke M113
Pepsi M114
Sprite M115
Cola M116
For instance, if it matches "Coke" and M113, then AAC should have "Widget".
For Pepsi and M114, then AAC might be "Stuff", etc.
On another tab, I have range names to capture the return value. The formula
(which works on a single cell) I have in Col A (AAC) is
{=INDEX(AAC, MATCH(B2&C2,Company&Type,0))}
On the other tab, I have the lookup values (with range names). See following:
AAC Company Type
Widget Coke M113
Stuff Pepsi M114
More Stuff Sprite M115
Books Cola M116
Cans Coke M123
Bottles Pepsi M124
Chairs Sprite M125
Lights Cola M126
Again, my formula works if I use it on one cell (using CTRL+SHIFT+ENTER),
but when I copy to other rows I get the same result as the orginal (copied
cell). I know it is an easy solution, but its not working for me.
I appreciate any help.
- References:
- Copy array formulas
- From: Scott A
- RE: Copy array formulas
- From: Sheeloo
- RE: Copy array formulas
- From: Scott A
- Copy array formulas
- Prev by Date: Re: Using range with INDIRECT and ROW()
- Next by Date: Re: Autofill list
- Previous by thread: RE: Copy array formulas
- Next by thread: group of dates
- Index(es):
Relevant Pages
|
