How to spill multiple matches with defined name as range?

[Rnkhch]

Hello,

I defined two dynamic ranges (names) through INDIRECT like this:

SampleNames=INDIRECT("Main!$D$2:$D$"&Control!$C$2)
SampleAverages=INDIRECT("Main!$H$2:$H$"&Control!$C$2)
where the end of the range is specified by Control!$C$2

Now I want to use these names and get the averages for specific samples, but some samples exist more than once, and I want to get a spill of all available averages. I was thinking to use INDEX within AGGREGATE, but it doesn't work, but maybe I'm overthinking this or missing something:

B2=AGGREGATE(15,3,INDEX(SampleAverages,(SampleNames=A2)/(SampleNames=A2),1),3)

I put 3 for the count, but I was also wondering what the best way is to put a counter for k?

Book1
	A	B	C	D	E	F	G	H	I
1				Names				Averages
2	test1:			test1				13
3				test2				89
4				test3				39
5				test1				13
6				test1				13
7				test6				58
8				test7				19
9
Sheet1

(There are other columns in the actual data, but I'm skipping here.)

I would appreciate any input!

 

[StephenCrump]

Will this work for you:

=AVERAGE(FILTER(SampleAverages,SampleNames=A2))

You said average, so I'm not sure why you're trying AGGREGATE(15, ...)

And are your samples the same size, so that it is valid to average your averages without weighting?

 

[StephenCrump]

On re-reading, I think you maybe wanted one formula:

	A	B	C	D	E	F	G	H
1				Names				Averages
2	test1	13	8	test1				13
3	test2	89		test2				89
4	test3	39		test3				39
5	test6	58		test1				13
6	test7	19		test1				13
7				test6				58
8				test7				19
Main

Cell Formulas
Range	Formula
A2:A6	A2	=UNIQUE(D2:D8)
B2:B6	B2	=IFERROR(BYROW(A2#,LAMBDA(r,AVERAGE(FILTER(SampleAverages,SampleNames=r)))),"n/a")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
SampleNames	=INDIRECT("Main!$D$2:$D$"&Main!$C$2)	A2

 

[Rnkhch]

Thanks. Maybe I should have stated more clearly, but I don't need to AVERAGE the numbers. I need to generate the list of those numbers (in this case they happen to be average of samples).

That's the reason I used INDEX. It works well when there is only one sample with the indicated name. But when there are multiple, I found online that people used SMALL or AGGREGATE 15 to return the list of multiple matches, and I was trying to emulate that here .

 

[StephenCrump]

OK thanks, in that case I'm not clear what the intended results look like.

Can you please illustrate?

 

[Rnkhch]

Oh yes, I should have put that manually in the XL2BB, but here is what I need in B2 (spill of the three numbers in H that correspond to test1 samples):

Book1
	A	B	C	D	E	F	G	H	I	J
1				Names				Averages
2	test1:	13		test1				13
3		27		test2				89
4		40		test3				39
5				test1				27
6				test1				40
7				test6				58
8				test7				19
9
Sheet1

If there was one "test1" sample, I would have used the regular INDEX/MATCH like this: B2=INDEX(SampleAverages,MATCH("test1",SampleNames,0),1)

So basically I need a fancier INDEX/MATCH for when there are multiple matches .

(I changed the numbers slightly here as I didn't realize I had 13 for all test1 samples in my original post.)

 

[StephenCrump]

In pseudo-formula speak, that's just: =FILTER(Averages,Names=A2).

Depending on what you're looking for, Averages might be defined in terms of data in column H, and Names column D.

Or perhaps: =FILTER(SampleAverages,SampleNames=A2))

 

[Rnkhch]

Oh, that's right. I totally missed the obvious . I was overthinking it . That's what happens after a full day of working on Excel

 

[StephenCrump]

Oh, that's right. I totally missed the obvious . I was overthinking it . That's what happens after a full day of working on Excel

He he. Time for a break perhaps?

It's nearly wine-time here.

 

[Rnkhch]

Yeah lol.

Incidentally, I liked your B2 formula in post #3. I always learn a lot form your formulas

Cheers