FILTER IF the criteria is within the set value (figures)

[ExcelNewbie2020]

what would be the formula if i want to filter the names that the numbers is within the first 20 in the list.
In my table the sum of name1-5 is 15 (column P). it is within the range of 20.

hlink.xlsm
	O	P	Q	R	S
1	NAME1	1			RESULT
2	NAME2	2			NAME1
3	NAME3	3			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME6	6			NAME5
7	NAME7	7
8	NAME8	8
9	NAME9	9
Sheet19

 

[Peter_SSs]

Does this do what you want?

23 04 29.xlsm
	O	P	Q	R	S
1	NAME1	1			RESULT
2	NAME2	2			NAME1
3	NAME3	3			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME6	6			NAME5
7	NAME7	7
8	NAME8	8
9	NAME9	9
First 20

Cell Formulas
Range	Formula
S2:S6	S2	=INDEX(O1:O9,SEQUENCE(MATCH(20,SCAN(0,P1:P9,LAMBDA(a,b,a+b)))))
Dynamic array formulas.

 

[ExcelNewbie2020]

Does this do what you want?

23 04 29.xlsm
	O	P	Q	R	S
1	NAME1	1			RESULT
2	NAME2	2			NAME1
3	NAME3	3			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME6	6			NAME5
7	NAME7	7
8	NAME8	8
9	NAME9	9
First 20

Cell Formulas
Range	Formula
S2:S6	S2	=INDEX(O1:O9,SEQUENCE(MATCH(20,SCAN(0,P1:P9,LAMBDA(a,b,a+b)))))
Dynamic array formulas.

thanks, it does work.. but i am hoping it would be simple as filtering certain values... what if the numbers 1-9 (column P) is not in ascending order?..

 

[fjns]

Hi, see the linked file (with an auxiliary column) for a possible solution...

Wrong cells visible on Google Drive, because Google Drive does not know the FILTER function. The formula works correctly with your Office 365.

The formula used in the table...
Q1: =SUM(P$1:P1) (Range: Q1:Q9)
S2: =FILTER(O1:O9,Q1:Q9<=20) (Range: S2:S6)

Filer.xlsx

 

[Peter_SSs]

but i am hoping it would be simple as filtering certain values... what if the numbers 1-9 (column P) is not in ascending order?..

Care to give another small set of sample data and the expected results with XL2BB and explain again in relation to that sample to help clarify exactly what you mean/want?

 

[ExcelNewbie2020]

Care to give another small set of sample data and the expected results with XL2BB and explain again in relation to that sample to help clarify exactly what you mean/want?

thank you sir, please see mini sheet, same raw data and expected result except that the raw data is not sorted.. the idea is if possible is to sort column P (ascending) first then filter column O if the sum total is less than 20 (column P) combine in a single formula

hlink.xlsm
	O	P	Q	R	S
1	NAME9	9			RESULT
2	NAME6	6			NAME1
3	NAME1	1			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME3	3			NAME5
7	NAME7	7
8	NAME8	8
9	NAME2	2
Sheet19

 

[Peter_SSs]

Thanks for the additional sample data and results. Give this version a try.

23 04 29.xlsm
	O	P	Q	R	S
1	NAME9	9			RESULT
2	NAME6	6			NAME1
3	NAME1	1			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME3	3			NAME5
7	NAME7	7
8	NAME8	8
9	NAME2	2
First 20 (2)

Cell Formulas
Range	Formula
S2:S6	S2	=LET(sl,SORT(O1:P9,2),INDEX(sl,SEQUENCE(MATCH(20,SCAN(0,INDEX(sl,0,2),LAMBDA(a,b,a+b)))),1))
Dynamic array formulas.

 

[ExcelNewbie2020]

Thanks for the additional sample data and results. Give this version a try.

23 04 29.xlsm
	O	P	Q	R	S
1	NAME9	9			RESULT
2	NAME6	6			NAME1
3	NAME1	1			NAME2
4	NAME4	4			NAME3
5	NAME5	5			NAME4
6	NAME3	3			NAME5
7	NAME7	7
8	NAME8	8
9	NAME2	2
First 20 (2)

Cell Formulas
Range	Formula
S2:S6	S2	=LET(sl,SORT(O1:P9,2),INDEX(sl,SEQUENCE(MATCH(20,SCAN(0,INDEX(sl,0,2),LAMBDA(a,b,a+b)))),1))
Dynamic array formulas.

and it worked like a charm.. thank you

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.