FILTER top n based on cell value

[nburaq]

Hi Gents,
I would like to filter my results based on the cell value (it will be week number) entered in A1 at Sheet1. Sheet2 includes the data to be filtered. I know how to filter top 5 data but I could not find where to set range for filtering. Thanks in advance for the comments and help!

Sample:Filter top nth value (lets say for top 5)
=FILTER(UDT!A:H;UDT!F:F>=LARGE(UDT!F:F;5))

Sheet1:

A1: 5 (Week Number)

Sheet2:

Day	Location	Problem	Hours	Week No
12/03/2022​	Floor1	Electrical	8​	10​
09/03/2022​	Floor1	Electrical	6​	10​
09/03/2022​	Floor1	Electrical	4​	8​
09/03/2022​	Floor3	Mechanical	3​	10​
09/03/2022​	Floor3	Mechanical	2​	6​
08/03/2022​	Floor3	Mechanical	2​	10​
08/03/2022​	Floor4	Mechanical	1​	10​
07/03/2022​	Floor4	Electrical	2​	10​
07/03/2022​	Floor4	Electrical	4​	10​
07/03/2022​	Floor2	Electrical	3​	10​
12/03/2022​	Floor2	Electrical	3​	10​
09/03/2022​	Floor2	Electrical	2​	11​
09/03/2022​	Floor1	Electrical	2​	12​
09/03/2022​	Floor1	Mechanical	1​	11​
09/03/2022​	Floor3	Mechanical	2​	13​
08/03/2022​	Floor3	Mechanical	7​	12​
08/03/2022​	Floor3	Mechanical	6​	12​
07/03/2022​	Floor3	Electrical	4​	12​
07/03/2022​	Floor2	Electrical	3​	13​
07/03/2022​	Floor2	Mechanical	2​	13​

 

[RoryA]

Just replace the 5 with A1, unless I misunderstood the question.

 

[Alex Blakenburg]

If you provide an XL2BB of your data, we woule be able to give a more precise answer but see if this is what you are after.
I have assumed you want the

• The large of hrs which I have as column E
• The filter on Week with the number being in A1 and the week column being F

=FILTER($A$5:$F$24,(LARGE(($F$5:$F$24=$A$1)*$E$5:$E$24,5)<=$E$5:$E$24)*($F$5:$F$24=$A$1),"No Data")

For a walk through see:
(6 mins)

 

[nburaq]

Just replace the 5 with A1, unless I misunderstood the question.

Hi Rory,
formula will filter the values based on week number entered in sheet1 A1 with top 5 results of hours

If you provide an XL2BB of your data, we woule be able to give a more precise answer but see if this is what you are after.
I have assumed you want the

• The large of hrs which I have as column E
• The filter on Week with the number being in A1 and the week column being F

=FILTER($A$5:$F$24,(LARGE(($F$5:$F$24=$A$1)*$E$5:$E$24,5)<=$E$5:$E$24)*($F$5:$F$24=$A$1),"No Data")

For a walk through see:
(6 mins)

Thanks a lot! This was exactly what I was looking for!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.