Copy array formulas

[Scott A]

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.

 

[Sheeloo]

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]

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]

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]

I did download your sample file. I did the same thing and it worked with my
sample build, just not with my actual file.

The following link is my actual data file

http://wikisend.com/download/540472/Breakout.xls

Col A is the column that I want to read two other cells to match. It should
look at the values in Col J & L. My ranges are on the other sheet.

If you can get it to work I would really appreciate it.

Scott

 

[Sheeloo]

There is nothing wrong with your formula... it is your data.

On Sheet1 you have Units as
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
A/1/160
B/1/160
B/1/160

Whereas on the second sheet you have
1/160
1/160
1/160
1/160
2/160
2/160
3/160
3/160
4/160
4/160
SOATC
SOATC
SOATC
SOATC
SOATC
ROAE

Notice the missing A/ before 1/160?

I have inserted A/ in all the rows upto 4/160.

The #N/A you see are because there are still no matches.

 

[Sheeloo]

Not sure whether I pasted the link...
Here it is, just in case I did not -
http://wikisend.com/download/566064/Breakout.xls

 

[Scott A]

Sorry for my delayed response. I see what you did. Agree that it works.
Part of my problem is linking to another users XLS which includes the
apha-prefix, but as you know does not show on the list. I have since edited
your formula to include parsing text so I now capture what you helped me with
and do not violate the integrity of the other users data.

Thank you for all your help. It works great.