LOOKUP THAT DEALS WITH MULTIPLE ROWS

[surkdidat]

I have the below code, which looks up a reference number, and then populates another sheet if the value is in that row. (This code works fine if every ID was only referenced once, howeve,r it falls out when more than one row with the same ID

However, there can be more than one row with the same reference number in the "Published" sheet, ie 10001, could be on rows 1,2,3, then 10002 just on row 4 etc.

I need this adapting so the match in the Published sheet, rather than having a row lookup (ie 8) does a match in column A of the Published Sheet, against column I in the ENG1 / Data Heet (column I)

=IF(ISNUMBER(
MATCH('DATA'!Q$2,Published!8:8,0))=TRUE,
INDEX('ENG1'!Q:Q,MATCH('DATA'!$I9,'ENG1'!$I:$I,0),),"")

'PUBLISHED

ID	DETAIL_1	DETAIL_2	DETAIL_3
10001	Colour	Size	Material
10002	Colour
10003	Colour	Material

ENG1

COLUMN I	COLUMN Q	COLUMN R	COLUMN S	COLUMN T
ID	Colour	Size	Material
10001	Yellow	555	Metal
10002	Brown
10003	Red
10003	Red	Paper

PUBLISHED (What I need)

COLUMN 1	COLUMN q	COLUMN R	COLUMN S
ID (Already on Sheet for Look Up)	Colour	Size	Material
10001	Yellow	555
10002	Brown
10003	Red
10004	Red	Paper

 

[surkdidat]

In other words ENG1 and Published have to mirror the cell content. So Q3 matches on both sheets, Q4 matches both sheets, R3 matches R3 etc.

 

[surkdidat]

I have solved my own question by using a separate sheet and copying all the IDS, and then INDEX(MATCH this against the attribute sheet, so whenI come to do a replica, it looks up the exact row in the data