Create a list using Index Aggregate with criteria from 2 separate columns.

[Silverhorne]

Hello,
I've been searching to see if there is a way to achieve the list in column G when data is added to the the cells of column D.
Can the criteria be set to C2:C10 & E2:E10 instead of C2:E10?
I created a formula to populate the correct ID to the item number, any suggestions for a better method?
Thanks for any help in advance!

ITEM	ID 1	DATA	ID 2
111-MS-1-24-2	LH		D16		111-MS-1-24-2	LH
111-MS-1-24-3	K3				111-MS-1-24-2	D16
111-MS-1-24-4	LH		P2		111-MS-1-24-3	K3
111-MS-1-24-5	RC				111-MS-1-24-4	LH
111-MS-1-24-6	LH		RC		111-MS-1-24-4	P2
					111-MS-1-24-5	RC
					111-MS-1-24-6	LH
					111-MS-1-24-6	RC

"G2"=IFERROR(@INDEX(B$2:B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/($C$2:$E$10>0),ROWS($A$1:$A1))),"")
"H2"=IFERROR(IF(G2=G1,INDEX($B$2:$E$10,MATCH(G2,$B$2:$B$10,0),4),INDEX($B$2:$E$10,MATCH(G2,$B$2:$B$10,0),2)),"")

 

[Silverhorne]

Sorry, not sure why XL2BB isn't posting the headers and formulas, it was working for my last post. Mini sheet crashes my excel??
The items column is actually column B.

 

[Silverhorne]

Copied to a new workbook and it didn't crash, started a new tab in the original workbook and it had a lot of array formulas that it must not have liked.

Book1
	A	B	C	D	E	F	G	H
1		ITEM	ID 1	DATA	ID 2
2		111-MS-1-24-2	LH		D16		111-MS-1-24-2	LH
3		111-MS-1-24-3	K3				111-MS-1-24-2	D16
4		111-MS-1-24-4	LH		P2		111-MS-1-24-3	K3
5		111-MS-1-24-5	RC				111-MS-1-24-4	LH
6		111-MS-1-24-6	LH		RC		111-MS-1-24-4	P2
7							111-MS-1-24-5	RC
8							111-MS-1-24-6	LH
9							111-MS-1-24-6	RC
10
Sheet2

Cell Formulas
Range	Formula
G2:G10	G2	=IFERROR(@INDEX(B$2:B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/($C$2:$E$10>0),ROWS($A$1:$A1))),"")
H2:H10	H2	=IFERROR(IF(G2=G1,INDEX($B$2:$E$10,MATCH(G2,$B$2:$B$10,0),4),INDEX($B$2:$E$10,MATCH(G2,$B$2:$B$10,0),2)),"")

 

[mart37]

A workaround is to mark the cells in the range TRUE or FALSE (when needed or not needed).
I use the columnnumbers to detect TRUE or FALSE.

=IFERROR(INDEX(B$2:B$10,AGGREGATE(15,6,(ROW($B$2:$B$10)-ROW($B$2)+1)/(($C$2:$E$10>0)*((COLUMN(C2:E10)<>4)*(ROW(C2:C10)-1)>0)),ROWS($A$1:$A1))),"")

 

[Silverhorne]

Thanks mart37, works great!
I actually learned a lot about the rows and columns functions that I was unsure of before.
The sheet I am using this in has a lot more rows and columns, I had to evaluate formula to figure out the 4 was a constant column to flag as a false, I should have read your second statement more carefully.