Return All Small Value in Range

[andersen_yunan]

Hi,

Is there any way that I could return the value in a range with given certain condition. For instance:

[TABLE="width: 192"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Ali[/TD]
[TD]A[/TD]
[TD="align: right"]89[/TD]
[/TR]
[TR]
[TD]Alvin[/TD]
[TD]B[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]C[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Cynthia[/TD]
[TD]A[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Derry[/TD]
[TD]C[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Kalvin[/TD]
[TD]B[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Ricky[/TD]
[TD]A[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]If Less Than 80,[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Score[/TD]
[/TR]
[TR]
[TD]Alvin[/TD]
[TD]B[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD]Barry[/TD]
[TD]C[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD]Derry[/TD]
[TD]C[/TD]
[TD="align: right"]77[/TD]
[/TR]
[TR]
[TD]Ricky[/TD]
[TD]A[/TD]
[TD="align: right"]68[/TD]
[/TR]
</tbody>[/TABLE]


Thanks in Advance!

 

[Trebor76]

You could just filter on Score to only show the results less than 80. The filtered data set can be copied elsewhere if needed.

 

[andersen_yunan]

You could just filter on Score to only show the results less than 80. The filtered data set can be copied elsewhere if needed.

Hi, Is there any excel formula to enable this?
I can't use filter on this since it will be hidden sheet and the data will keep changing upon new data entry.

 

[Peter_SSs]

Hi, Is there any excel formula to enable this?

Formula in A12 copied down as far as you might need.
B12 copied across and down the same number of rows.

Excel Workbook
	A	B	C
1	Name	Class	Score
2	Ali	A	89
3	Alvin	B	78
4	Barry	C	23
5	Cynthia	A	#N/A
6	Derry	C	77
7	Kalvin	B	#N/A
8	Ricky	A	68
9
10
11	Name	Class	Score
12	Alvin	B	78
13	Barry	C	23
14	Derry	C	77
15	Ricky	A	68
16
Small Values

 

[andersen_yunan]

Formula in A12 copied down as far as you might need.
B12 copied across and down the same number of rows.

Small Values

	A	B	C
Name	Class
Ali	A
Alvin	B
Barry	C
Cynthia	A	#N/A
Derry	C
Kalvin	B	#N/A
Ricky	A
Name	Class	Score
Alvin	B
Barry	C
Derry	C
Ricky	A

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:63px;"><col style="width:52px;"><col style="width:53px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]Score[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]89[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]78[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="align: right"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="align: right"]77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="align: right"]68[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A12 =IFERROR(INDEX(A$2:A$8,AGGREGATE(15,6,(ROW(A$2:A$8)-ROW(A$2)+1)/(C$2:C$8<80),ROWS(A$12:A12))),"") B12 =IF($A12="","",INDEX(B$2:B$8,MATCH($A12,$A$2:$A$8,0))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks for the prompt response! it works

 

[Peter_SSs]

Thanks for the prompt response! it works

You're welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.