Array formula not bringing correct data

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

{=IF(ISERROR(INDEX(Sheet1!K:L,SMALL(IF(Sheet1!L:L<>"",ROW(Sheet1!K:K)), ROW(Sheet1!2:2)),1)),"",INDEX(Sheet1!K:L,SMALL(IF(Sheet1!L:L<>"",ROW(Sheet1!K:K)), ROW(Sheet1!2:2)),1))}

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

{=INDEX(Sheet1!K:L, SMALL(IF(Sheet1!K:K=A3, ROW(Sheet1!K:K)), ROW($1:$1)), 2)}

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.

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

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.

...INDEX(Sheet1!K:L,SMALL(IF(Sheet1!L:L<>"",ROW(Sheet1!K:K)), ROW(Sheet1!2:2)),2))}

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.