Alteration to formula with additional argument

[StellaArtois]

Hi all,

Happy new year to everyone!

I wonder if i may ask for some help please

I have the following formula, which works for a single argument BD1 but i need to include BD2 also, so it returns both BD1 and BD2 results

=IFERROR(INDEX($BD$5:$BD$502,SMALL(IF($AC$5:$AC$502=$BG$1,IF($U$5:$U$502=$BD$2,ROW($AA$5:$AA$502)-ROW($AA$5)+1)),ROWS(BH$5:BH5))),"")

Many thanks in advance

 

[Kaper]

Seems that formula reacts now on BD2, not BD1. Anyway ... how about:

=IFERROR(INDEX($BD$5:$BD$502,SMALL(IF($AC$5:$AC$502=$BG$1,IF(($U$5:$U$502=$BD$2)+($U$5:$U$502=$BD$1),ROW($AA$5:$AA$502)-ROW($AA$5)+1)),ROWS(BH$5:BH5))),"")

 

[Peter_SSs]

I assume that formula is to be copied down? Since you have Excel 365 what about this in the top cell and delete all the formulas below and it should return all the relevant results without needing to copy down.

=FILTER(BD5:BD502,(AC5:AC502=BG1)*((U5:U502=BD1)+(U5:U502=BD2)),"")

Here is my sample data and results

25 01 03.xlsm
	U								AC																											BD	BE	BF	BG	BH
1																																				6			a
2																																				3
3
4
5	9								b																											BD5				BD6
6	6								a																											BD6				BD9
7	3								b																											BD7				BD10
8	3								c																											BD8				BD11
9	3								a																											BD9				BD15
10	6								a																											BD10				BD16
11	6								a																											BD11
12	9								b																											BD12
13	9								b																											BD13
14	6								c																											BD14
15	3								a																											BD15
16	3								a																											BD16
17	6								b																											BD17
18	9								a																											BD18
19
Filter

Cell Formulas
Range	Formula
BH5:BH10	BH5	=FILTER(BD5:BD502,(AC5:AC502=BG1)*((U5:U502=BD1)+(U5:U502=BD2)),"")
Dynamic array formulas.

 

[StellaArtois]

Wow Peter_SSs,

That is awesome, thanks
Does this formula return text also?

Thanks for your answer Kaper

 

[Peter_SSs]

Does this formula return text also?

It already does in my sample above.
.. or do I not understand your question?

Alternatively, why not just try it with whatever example/data you are thinking of?