Hi Shane,

The fancier formula isn't working according to my need.

I've used the ISERROR function to remove the #NUM! errors.

Thanks so much.

=(IF(ISERROR(INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW( B$1:B$6),""),ROW()))),"",INDEX(A$1:A$6,SMALL(IF(B$ 1:B$6<2,ROW(B$1:B$6),""),ROW()))))

"Shane Devenshire" wrote:

Hi,

Try this array formula in H1:

=INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),"") ,ROW()))

array - you must press Shift+Ctrl+Enter to enter the formula. In I1:

=INDEX(A$1:A$6,LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),"" ),ROW()))

Drag these formulas down as far as necessary. They will return #NUM! errors
after the last item is found, to prevent that you can get fancier with:

=IF(ROW()<=COUNTIF($B$1:$B$6,"<2"),INDEX(A$1:A$6,S MALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW())),"")

and

=IF(ROW()<=COUNTIF($B$1:$B$6,"=6"),INDEX(A$1:A$6, LARGE(IF(B$1:B$6=6,ROW(B$1:B$6),""),ROW())),"")

both array entered. These can be copied down as far as you wish.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Sasikiran" wrote:

Dear,

I am looking for a formula to list out the names in a column only if it
meets the set criteria.

Column A Column B ColumnH Column I
ABC 4 BCD EFG
BCD 1.5 DEF FGH
CDE 5
DEF 1
EFG 6
FGH 8

Column A has the names and column B has their scores. I want to list all the
names whose score is less than 2 in column H and the list of names whose
score is greater than or equal to 6 in column I.

I could able to do that using a simple IF statement but it is leaving a
blank value if it is more than 2. Like wise in column I it is leaving a blank
cell if it less than 2.

=IF(AND(B10,B1<2),B1,"")

I wanted to get only the names without any blanks.

Please suggest me on this.

Thank you.