Two Column match, with 1 result return, displaying horizontally.

[JohnnyIsNerdy]

Hello,

I've tried small, large, aggregate.. I just can't get it right.

My goal is to have a list on a separate sheet, but I tried on the same sheet as the data to see if it can be done first.

Look for ID within the list and match, check if that ID matches a criteria in another column (other than ID), and return all results horizontally sorted large to small.

1. criteria ID and owner (has 3 or 4 , so maybe an or )
2. return a value in another column
3. display horizontally in order of large to small.

I used aggregate(14,6,...) / (criteria...)

Any help out there would be great. I've tried to follow an example on here but I can't get multiple results using array format.

I'll post an example snippet of the data for more clarification.

 

[alansidman]

For clarity, please post a sample of what you have and then what you want. Use XL2BB to do this as we cannot manipulate data in a picture.

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Values			ID	Numbers		ID	Numbers
2	A001	45			A005	62		A005	62	53	50
3	A002	46				53
4	A003	47				50
5	A004	48
6	A001	49
7	A005	50
8	A003	51
9	A002	52
10	A005	53
11	A002	54
12	A004	55
13	A004	56
14	A001	57
15	A002	58
16	A003	59
17	A004	60
18	A001	61
19	A005	62
20
Sheet1

Cell Formulas
Range	Formula
I2:O2	I2	=IFERROR(AGGREGATE(14,6,$B$2:$B$19/($A$2:$A$19=$E$2),COLUMNS($H$1:H1)),"")
F2:F8	F2	=IFERROR(AGGREGATE(14,6,$B$2:$B$19/($A$2:$A$19=$E$2),ROWS($E$1:E1)),"")

 

[Fluff]

Maybe one of these.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Values				ID	Numbers
2	A001	45	Y			A002	54	52	46
3	A002	46	Y				54	52	46
4	A003	47	Y
5	A004	48	Y
6	A001	49	Y
7	A005	50	Y
8	A003	51	Y
9	A002	52	Y
10	A005	53	N
11	A002	54	Y
12	A004	55	N
13	A004	56	N
14	A001	57	Y
15	A002	58	N
16	A003	59	N
17	A004	60	Y
18	A001	61	N
19	A005	62	Y
Lists

Cell Formulas
Range	Formula
G2:L2	H2	=IFERROR(AGGREGATE(14,6,$B$2:$B$19/($A$2:$A$19=$F2)/($C$2:$C$19="Y"),COLUMNS($G$1:H1)),"")
G3:I3	G3	=TRANSPOSE(SORT(FILTER(B2:B19,(A2:A19=F2)*(C2:C19="Y")),,-1))
Dynamic array formulas.

 

[JohnnyIsNerdy]

Welcome to MrExcel Message Board.
Try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Values			ID	Numbers		ID	Numbers
2	A001	45			A005	62		A005	62	53	50
3	A002	46				53
4	A003	47				50
5	A004	48
6	A001	49
7	A005	50
8	A003	51
9	A002	52
10	A005	53
11	A002	54
12	A004	55
13	A004	56
14	A001	57
15	A002	58
16	A003	59
17	A004	60
18	A001	61
19	A005	62
20
Sheet1

Cell Formulas
Range	Formula
I2:O2	I2	=IFERROR(AGGREGATE(14,6,$B$2:$B$19/($A$2:$A$19=$E$2),COLUMNS($H$1:H1)),"")
F2:F8	F2	=IFERROR(AGGREGATE(14,6,$B$2:$B$19/($A$2:$A$19=$E$2),ROWS($E$1:E1)),"")

What does that last array mean? it looks like it's naming the header for the ID.
This is what I put, is this wrong? I'm not getting multiple results.

=IFERROR(AGGREGATE(14,6,$CO$4:$CO$28/(($CE$4:$CE$28=$CX4)*($CL$4:$CL$28=$CO$1)),COLUMNS($CX$3:CX3)),"")

Any idea what I'm doing wrong here?

 

[JohnnyIsNerdy]

So I'm trying to get the id to match and the owner to be specific, if it checks out, i return all the heights.

 

[Fluff]

You need to drag that formula to the right. With 365 you could also use the formula I showed in G3 which will automatically spill to the right.

 

[JohnnyIsNerdy]

You need to drag that formula to the right. With 365 you could also use the formula I showed in G3 which will automatically spill to the right.

SWEET!! Woo hoo! For "or", do I just do a + instead of *?

 

[Fluff]

Which formula are you referring to?

 

[JohnnyIsNerdy]

This is what I ended up using.

I should be able to get the lowest, but I think I'm going to use SMALL this time, since I found out aggregate can't use labels.

Also, this is an off topic, I was referencing a worksheet that will be deleted and added, but same name. I was getting REF cause it was no longer there, so I resorted to to using indirect with the name of the worksheet. Is there a better way to do it? That's the only way I found, but didn't look to hard since it worked lol. It's so crazy how powerful excel is and with vba too.

If I had only resorted to registering and posting here sooner LOL :D

=IFERROR((AGGREGATE(14,6,$BP$4:$BP$2003/(($BF$4:$BF$2003=$CW4)*(($BM$4:$BM$2003='Static Names'!$Q$3)+($BM$4:$BM$2003='Static Names'!$Q$4)+($BM$4:$BM$2003='Static Names'!$Q$5))),COLUMNS($CW3:CW3))),"")