Array formula not bringing correct data

[Nikeyg]

Hi guys
It's been a while since my last post, but I've hit a wall and am desperate for help.
I'm using an array formula to bring through a list of products from one sheet to another.

Here's the data I am trying to extrapolate:
Bellway 87.5
BKG 87.5
BARC 44.34
VED 41.76
BT 44
BKG 43.61
VOD 44.07
CPG 43.58
IHG 43.6
BATS 320.66
STAN 239.77
VED 179.91
DGE 83.26
EMG 45.26
GFS 212
UU 79.2

Now, that data is in 'Sheet1'
In a sheet labelled Equities, I am using this formula

Please Login or Register  to view this content.

This then brings through the list of names in column A.
However, in column B I am trying to use another array formula to bring through the data in column L. For which, I am using this formula

Please Login or Register  to view this content.

It pulls the figures through, but, when it comes to duplicated entries (ie the BKG and the VED), it's only pulling through the first figure, for both the first and second time that item appears.

Am I missing something really simple to get it to bring through the second item for the second instance something comes up?


Thanks in advance.

[Special-K]

For a start, dont do the whole column it will slow things down, enter a row number in the formula

[Nikeyg]

Have tried that. I get a #REF error when I do.

[jhren]

If the data you want in column B is from "other sheet" L, and across from data in column A from "other sheet" K, then use the same formula as in A and change the last index function's column offset to 2.

Please Login or Register  to view this content.

[icestationzbra]

wherever it is that you start with this formula:

Please Login or Register  to view this content.

use the following ARRAY formula instead:

Please Login or Register  to view this content.

it is counter-productive to ask for your formula to be fixed without sharing a sample file. as far as i can see, your formulae are sub-optimal. if you share the sample file, efficiency and readability could be improved.

[Nikeyg]

This works.
Not quite how I wanted to do it, but it gets the desired result.

Thanks.


Sorry Icest. Normally I do post samples, but was in such a rush as we're short staffed at work that I couldn't provide one without compromising data in it.

[newdoverman]

I shortened the ranges to match the small sample given and changed the cell range references to absolute. This is the revised version of the first formula that I came up with:

Please Login or Register  to view this content.

The second formula:

Please Login or Register  to view this content.